Postgre常用数据类型一

数字类型

  1.serial 一般用作自增的主键。

   但是如果表里面字段id的类型是serial,默认格式插入两条数据则下一条数据的id默认值是3。即使手动插入一条id为3的记录，该字段的下一条仍然是3。

  2.PostgreSQL中数组元素的下标是从1开始n结束,支持数组下标的DML,如select smallintArray[1] from table_number。

  3.smallint数组的两种初始化方式,其余的可以类比--> '{1,2}' 或者ARRAY[1,2,3]  

   这里要强调下关于character varying[]数组类型，Postgre中单引号需要转义'-->'' 

   所以字符型数组的表示方式'{''name'',''sex''}'

   如果name中间有个单引号怎么办呢？同样的'{''na''me'',''sex''}'

 4.下面列表中serial smallserial bigserial没有数组类型，其余的都有数组类型。

 

名字 存储空间 描述 范围

smallint	2字节	小范围整数	-32768到+32768
integer	4字节	常用的整数	-2147483648到+2147483647
bigint	8字节	大范围的整数	-9223372036854775808到9223372036854775807
decimal	变长	用户声明精度，精确	无限制  无该类型
numeric	变长	用户声明长度，精度	无限制
real	4字节	变精度，不精确	6位十进制数字精度
double precision	8字节	变精度，不精确	15位十进制数字精度
smallserial	2字节	小范围整数	-32768到+32768
serial	4字节	自增整数	1到2147483647
bigserial	8字节	大范围的自增整数	1到9223372036854775807
int2vector	int数组

 int2vector的写入方法

 方法1：ARRAY[12,23]::int2vector           在后面带上类型描述，有点小蛋疼。

 方法2：'12  23 32'                        用空格分隔

      

     

货币 

  money money[] 

 

二进制类型

     1. bytea就是sql中的blob Bytea两种输入有

        第一种字符串特俗字符需要转义 

        第二种16进制 E'\\xDEADBEEF' 两个反斜杠前面要一个E开头，否则会将'\\xDEADBEEF'中的每一个字符当成一个字节来写入

     2. 定义bit(3)如果插入'01'则会报错不会自动补齐。 ERROR: bit string length 2 does not match type bit(3)

     bytea   bytea[] bit  bit[] 【bit varying】 【bit varying[]】

 

布尔

   boolean  boolean[] 

 

字符

   

补充，目前9.4版本pgadmin没有找到varchar类型，且char类型不可设置长度，长度为1，但是如果直接写SQL语句指定长度是可以执行成功的。

character varying(n), varchar(n)	有限制的可变长度
character(n), char(n)	定长，不足补空白
text	变长，无限制

 

 

xml    

 1.xml类型 本地环境不输入xml格式,数据也是可以进去的，xml类型对一个文档类型声明（DTD）不会验证输入值，即使输入值声明了一个DTD。

 2.要使用这个数据类型，编译时必须使用configure --with-libxml。 

  示例:

  -- 将字符串转化成xml

  XMLPARSE ( { DOCUMENT | CONTENT } value)

  select XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')

  select XMLPARSE (CONTENT  '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')

  -- 将xml转化成字符串类型 value为xml类型，type可以是character，character varying或text (或其中某个的变种)。

  XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

  -- 设置修改xml函数XMLPARSE和XMLSERIALIZE默认的会话级别的参数

  SET XML OPTION { DOCUMENT | CONTENT };

 

 xml xml[]

 

 

json

    1.JSON和JSONB有各自的优缺点，根据不同的场景选择使用。

      JSON格式的数据对于写入和修改速度比较快，而对于处理的比较慢，因为需要解析成二进制数据。而JSONB则相反写入和修改比较慢，处理相对较快，同时jsonb支持索引。

      所以如果对于写操作比较多的话建议选择JSON，如果读操作比较多的话，建议选择JSONB。

    2.JSONB保存的数据相对更少，因为它不会保存JSON文本串中的无关语法的空格，重复的key(最后一个key,value）为有效值，以及key的顺序。

    3.JSON文本转化成JSONB文本，JSONB其实是RFC 7159的一些原始类型组成的，如String,Numeric，boolean都有些限制，如精度问题。

    4.JSONB的索引主要分为两种gin(column)，gin(column jsonb_path_ops)

       gin支持的操作符@>, ?, ?& and ?|  但是gin索引相对较慢因为它会对每个key建立一个索引。如{a:{b:c}}，分别对a,c,c建立独立索引。

       jsonb_path_ops仅支持@> 它会对上面的a,b,c进行合并生成一个hash值来索引，所以它不支持覆盖索引扫描。

       详细例子见下面SQL测试，但是有点奇怪执行计划中并没有体现出索引，只出现filter。

 

   SQL测试：

    -- Simple scalar/primitive value

    -- Primitive values can be numbers, quoted strings, true, false, or null

       SELECT '5'::json;

    -- Array of zero or more elements (elements need not be of same type)

       SELECT '[1, 2, "foo", null]'::json;

    -- Object containing pairs of keys and values

    -- Note that object keys must always be quoted strings

       SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

    -- Arrays and objects can be nested arbitrarily

       SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

    --比较发现jsonb默认去掉了空格，同时没有保持原来的key顺序

      SELECT '{"bar": "baz", "balance": 7.77,    "active":false}'::json; 

         "{"bar": "baz", "balance": 7.77,    "active":false}"

      SELECT '{"bar": "baz", "balance": 7.77,    "active":false}'::jsonb;

        "{"bar": "baz", "active": false, "balance": 7.77}" 

    -- json超出numeric范围精度丢失

      SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;

         "{"reading": 1.230e-5}";"{"reading": 0.00001230}"

 

   -- 关于包含操作符@>  右边的部分是否包含在左边的部分

   -- Simple scalar/primitive values contain only the identical value:

     SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

   -- The array on the right side is contained within the one on the left:

     SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

   -- Order of array elements is not significant, so this is also true:

     SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

   -- Duplicate array elements don't matter either:

     SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

   -- The object with a single pair on the right side is contained

   -- within the object on the left side:

     SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;

   -- The array on the right side is not considered contained within the

   -- array on the left, even though a similar array is nested within it:

     SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

   -- But with a layer of nesting, it is contained:

     SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

   -- Similarly, containment is not reported here:

     SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

     SELECT '{"foo": {"bar": "baz"},"bar": "baz"}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields true

   -- This array contains the primitive string value:  return true

     SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

   -- This exception is not reciprocal -- non-containment is reported here:

     SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

 

   --存在操作符？  

   -- String exists as array element:

     SELECT '["foo", "bar", "baz"]'::jsonb ? 'baz';

   -- String exists as object key:

     SELECT '{"foo": "bar"}'::jsonb ? 'foo';

   -- Object values are not considered:

     SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

     SELECT '{"foo": "bar","bar":"test"}'::jsonb ? 'bar';  -- yields true

   -- As with containment, existence must match at the top level:

     SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

   -- A string is considered to exist if it matches a primitive JSON string:

     SELECT '"foo"'::jsonb ? 'foo';

   

  --关于jsonb的索引

    create table api (

      jdoc jsonb

    )

    insert into api(jdoc) values('{

        "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",

        "name": "Angela Barton",

        "is_active": true,

        "company": "Magnafone",

        "address": "178 Howard Place, Gulf, Washington, 702",

        "registered": "2009-11-07T08:53:22 +08:00",

        "latitude": 19.793713,

        "longitude": 86.513373,

        "tags": [

            "enim",

            "aliquip",

            "qui"

        ]

    }');

    CREATE INDEX idxgin ON api USING gin (jdoc);

    -- DROP INDEX idxgin;

    CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

    -- DROP INDEX idxginp;

    -- Find documents in which the key "company" has value "Magnafone"

    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

    -- Find documents in which the key "tags" contains key or array element "qui"

    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

 

 

 备注部分引用来自Postgre9.0中文手册和http://www.postgresql.org/。