数据类型

> CREATE TABLE a (b STRING[]);

> INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);

> SELECT * FROM a;

+----------------------+
|          b           |
+----------------------+
| {"sky","road","car"} |
+----------------------+
(1 row)

> CREATE TABLE c (d INT ARRAY);

> INSERT INTO c VALUES (ARRAY[10,20,30]);

> SELECT * FROM c;

+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)

> SELECT * FROM c;

+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)

> SELECT d[2] FROM c;

+------+
| d[2] | 
+------+
|   20 |
+------+
(1 row)

> SELECT * FROM c;

+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)

> UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;

> SELECT * FROM c;

+---------------+
|       d       |
+---------------+
| {10,20,30,40} |
+---------------+
(1 row)

> SELECT * FROM c;

+---------------+
|       d       |
+---------------+
| {10,20,30,40} |
+---------------+
(1 row)

> UPDATE c SET d = d || 50 WHERE d[4] = 40;

> SELECT * FROM c;

+------------------+
|        d         |
+------------------+
| {10,20,30,40,50} |
+------------------+
(1 row)

> SELECT ARRAY[true,false,true]::INT[];

   array
+---------+
  {1,0,1}
(1 row)

> SELECT ARRAY[true,false,true]::TIMESTAMP[];

pq: invalid cast: bool[] -> TIMESTAMP[]

> SELECT ARRAY[1,NULL,3]::string;

    array
+------------+
  {1,NULL,3}
(1 row)

> SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;

               array
+----------------------------------+
  {"(1,\"a b\")","(2,\"c\"\"d\")"}
(1 row)

> CREATE TABLE b (x BIT, y BIT(3), z VARBIT, w VARBIT(3));

> SHOW COLUMNS FROM b;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  x           | BIT       |    true     | NULL           |                       | {}        |   false    
  y           | BIT(3)    |    true     | NULL           |                       | {}        |   false    
  z           | VARBIT    |    true     | NULL           |                       | {}        |   false    
  w           | VARBIT(3) |    true     | NULL           |                       | {}        |   false    
  rowid       | INT8      |    false    | unique_rowid() |                       | {primary} |   true     
(5 rows)

> INSERT INTO b(x, y, z, w) VALUES (B'1', B'101', B'1', B'1');

> SELECT * FROM b;

  x |  y  | z | w
+---+-----+---+---+
  1 | 101 | 1 | 1

> INSERT INTO b(x) VALUES (B'101');

pq: bit string length 3 does not match type BIT

> INSERT INTO b(y) VALUES (B'10');

pq: bit string length 2 does not match type BIT(3)

> INSERT INTO b(w) VALUES (B'1010');

pq: bit string length 4 too large for type VARBIT(3)

> CREATE TABLE bool (a INT PRIMARY KEY, b BOOL, c BOOLEAN);

> SHOW COLUMNS FROM bool;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | INT8      |    false    | NULL           |                       | {primary} |   false    
  b           | BOOL      |    true     | NULL           |                       | {}        |   false    
  c           | BOOL      |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO bool VALUES (12345, true, CAST(0 AS BOOL));

> SELECT * FROM bool;

+-------+------+-------+
	a   |  b   |   c    
+-------+------+-------+
  12345 | true | false  
(1 row)

> CREATE TABLE bytes (a INT PRIMARY KEY, b BYTES);

-- explicitly typed BYTES literals
INSERT INTO bytes VALUES (1, b'\141\142\143'), (2, b'\x61\x62\x63'), (3, b'\141\x62\c');

-- string literal implicitly typed as BYTES
INSERT INTO bytes VALUES (4, 'abc');


SELECT * FROM bytes;

+---+-----+
| a |  b  |
+---+-----+
| 1 | abc |
| 2 | abc |
| 3 | abc |
| 4 | abc |
+---+-----+
(4 rows)

> SELECT length('☃'::string);

  length
+--------+
       1

> SELECT length('☃'::bytes);

  length
+--------+
       3

> CREATE TABLE foo (a STRING COLLATE en PRIMARY KEY);
-- Note: STRING 可用任意别名

> INSERT INTO foo VALUES ('dog' COLLATE en);

> CREATE TABLE de_names (name STRING COLLATE de PRIMARY KEY);

> INSERT INTO de_names VALUES ('Backhaus' COLLATE de), ('Bär' COLLATE de), ('Baz' COLLATE de);

> SELECT * FROM de_names ORDER BY name;

    name
+----------+
  Backhaus
  Bär
  Baz
(3 rows)

> SELECT * FROM de_names ORDER BY name COLLATE sv;

    name
+----------+
  Backhaus
  Baz
  Bär
(3 rows)

> SELECT 'A' COLLATE de < 'Ä' COLLATE de;

  ?column?
+----------+
    true
(1 row)

> SELECT 'Ä' COLLATE sv < 'Ä' COLLATE de;
pq: unsupported comparison operator: <collatedstring{sv}> < <collatedstring{de}>

> SELECT CAST(name AS STRING) FROM de_names ORDER BY name;

    name
+----------+
  Backhaus
  Baz
  Bär
(3 rows)

> SELECT pg_collation_for('Bär' COLLATE de);

  pg_collation_for
+------------------+
  de
(1 row)

> SELECT COLLATION FOR ('Bär' COLLATE de);

  pg_collation_for
+------------------+
  de
(1 row)

> CREATE TABLE dates (a DATE PRIMARY KEY, b INT);

> SHOW COLUMNS FROM dates;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | DATE      |    false    | NULL           |                       | {primary} |   false    
  b           | INT8      |    true     | NULL           |                       | {}        |   false    
(2 rows)

> INSERT INTO dates VALUES (DATE '2016-03-26', 12345);

> INSERT INTO dates VALUES ('2016-03-27', 12345);

> SELECT * FROM dates;

+---------------------------+-------+
 			  a             |   b    
+---------------------------+-------+
  2016-03-26 00:00:00+00:00 | 12345  
  2016-03-27 00:00:00+00:00 | 12345 
+---------------------------+-------+
(2 rows)

> CREATE TABLE decimals (a DECIMAL PRIMARY KEY, b DECIMAL(10,5), c NUMERIC);

> SHOW COLUMNS FROM decimals;

  column_name |   data_type   | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+---------------+-------------+----------------+-----------------------+-----------+-----------+
  a           | DECIMAL       |    false    | NULL           |                       | {primary} |   false    
  b           | DECIMAL(10,5) |    true     | NULL           |                       | {}        |   false    
  c           | DECIMAL       |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO decimals VALUES (1.01234567890123456789, 1.01234567890123456789, 1.01234567890123456789);

> SELECT * FROM decimals;

            a            |    b    |           c             
+------------------------+---------+------------------------+
  1.01234567890123456789 | 1.01235 | 1.01234567890123456789  
(1 rows)

# The value in "a" matches what was inserted exactly.
# The value in "b" has been rounded to the column's scale.
# The value in "c" is handled like "a" because NUMERIC is an alias.

> CREATE TABLE floats (a FLOAT PRIMARY KEY, b REAL, c DOUBLE PRECISION);

> SHOW COLUMNS FROM floats;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | FLOAT8    |    false    | NULL           |                       | {primary} |   false    
  b           | FLOAT4    |    true     | NULL           |                       | {}        |   false    
  c           | FLOAT8    |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO floats VALUES (1.012345678901, 2.01234567890123456789, CAST('+Inf' AS FLOAT));

> SELECT * FROM floats;

        a        |         b          |  c    
+----------------+--------------------+------+
  1.012345678901 | 2.0123456789012346 | +Inf  
(1 row)
# Note that the value in "b" has been limited to 17 digits.

> CREATE TABLE computers (
    ip INET PRIMARY KEY,
    user_email STRING,
    registration_date DATE
  );

> SHOW COLUMNS FROM computers;

     column_name    | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  ip                | INET      |    false    | NULL           |                       | {primary} |   false    
  user_email        | STRING    |    true     | NULL           |                       | {}        |   false    
  registration_date | DATE      |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO computers
  VALUES
    ('192.168.0.1', 'info@google.com', '2018-01-31'),
    ('192.168.0.2/10', 'lauren@google.com', '2018-01-31'),
    ('2001:4f8:3:ba:2e0:81ff:fe22:d1f1/120', 'test@google.com', '2018-01-31');

> SELECT * FROM computers;

                   ip                  |    user_email     |     registration_date      
+--------------------------------------+-------------------+---------------------------+
  192.168.0.2/10                       | lauren@google.com | 2018-01-31 00:00:00+00:00  
  192.168.0.1                          | info@google.com   | 2018-01-31 00:00:00+00:00  
  2001:4f8:3:ba:2e0:81ff:fe22:d1f1/120 | test@google.com   | 2018-01-31 00:00:00+00:00  
(3 rows)

9223372036854775807 # INT default max value
   9007199254740991 # JS integer max value

> CREATE TABLE ints (a INT PRIMARY KEY, b SMALLINT);

> SHOW COLUMNS FROM ints;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | INT8      |    false    | NULL           |                       | {primary} |   false    
  b           | INT2      |    true     | NULL           |                       | {}        |   false    
(2 rows)

> INSERT INTO ints VALUES (1, 32);

> SELECT * FROM ints;

  a | b   
+---+----+
  1 | 32  
(1 row)

> CREATE TABLE intervals (a INT PRIMARY KEY, b INTERVAL);

> SHOW COLUMNS FROM intervals;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | INT8      |    false    | NULL           |                       | {primary} |   false    
  b           | INTERVAL  |    true     | NULL           |                       | {}        |   false    
(2 rows)

> INSERT INTO
    intervals
    VALUES (1, INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'),
           (2, INTERVAL '1-2 3 4:5:6'),
           (3, '1-2 3 4:5:6');

> SELECT * FROM intervals;

  a |               b                
+---+-------------------------------+
  1 | 1 year 2 mons 3 days 04:05:06  
  2 | 1 year 2 mons 3 days 04:05:06  
  3 | 1 year 2 mons 3 days 04:05:06  
(3 rows)

> CREATE TABLE users (
    profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    last_updated TIMESTAMP DEFAULT now(),
    user_profile JSONB
  );

> SHOW COLUMNS FROM users;

  column_name  | data_type | is_nullable |  column_default   | generation_expression |  indices  | is_hidden  
+--------------+-----------+-------------+-------------------+-----------------------+-----------+-----------+
  profile_id   | UUID      |    false    | gen_random_uuid() |                       | {primary} |   false    
  last_updated | TIMESTAMP |    true     | now():::TIMESTAMP |                       | {}        |   false    
  user_profile | JSONB     |    true     | NULL              |                       | {}        |   false    
(3 rows)

> INSERT INTO users (user_profile) VALUES
    ('{"first_name": "Zhang", "last_name": "San", "location": "Beijing", "online" : true, "friends" : 520}'),
    ('{"first_name": "Wang", "status": "Looking for treats", "location" : "ShangHai"}');

> SELECT * FROM users;

                profile_id              |          last_updated           |                                            user_profile                                             
+--------------------------------------+---------------------------------+----------------------------------------------------------------------------------------------------+
  8322a77e-84e3-4855-8a7a-f5a4adf5c6da | 2019-03-25 06:27:05.95072+00:00 | {"first_name": "Zhang", "friends": 520, "last_name": "San", "location": "Beijing", "online": true}  
  e6af0808-f4b3-4c6d-813b-f59553673711 | 2019-03-25 06:27:05.95072+00:00 | {"first_name": "Wang", "location": "ShangHai", "status": "Looking for treats"}                      
(2 rows)

> SELECT profile_id, last_updated, jsonb_pretty(user_profile) FROM users;

               profile_id              |           last_updated           |            jsonb_pretty             
+--------------------------------------+----------------------------------+------------------------------------+
  1740d746-2b14-41f7-b733-99c64de2de8b | 2020-03-25 06:48:51.240539+00:00 | {                                   
                                       |                                  |     "first_name": "Zhang",          
                                       |                                  |     "friends": 520,                 
                                       |                                  |     "last_name": "San",             
                                       |                                  |     "location": "Beijing",          
                                       |                                  |     "online": true                  
                                       |                                  | }                                   
  47f6134b-7ae7-4078-9c55-811d12905c39 | 2020-03-25 06:48:51.240539+00:00 | {                                   
                                       |                                  |     "first_name": "Wang",           
                                       |                                  |     "location": "ShangHai",         
                                       |                                  |     "status": "Looking for treats"  
                                       |                                  | }                                   
(2 rows)

> SELECT user_profile->'first_name' as first_name,user_profile->'location' as location FROM users;

  first_name |  location   
+------------+------------+
  "Wang"     | "ShangHai"  
  "Zhang"    | "Beijing"   
(2 rows)

> SELECT user_profile->>'first_name' as first_name,user_profile->>'location' as location FROM users;

  first_name | location  
+------------+----------+
  Wang       | ShangHai  
  Zhang      | Beijing   
(2 rows)

> CREATE TABLE student_profiles (
    id STRING PRIMARY KEY AS (profile->>'id') STORED,
    profile JSONB
);

> INSERT INTO student_profiles (profile) VALUES
    ('{"id": "d1001", "name": "Zhang San", "age": "15", "school": "RENDA", "sports": "none"}'),
    ('{"name": "Wang Wu", "age": "15", "id": "f3003", "school": "SIZHONG", "clubs": "Basketball"}'),
    ('{"name": "Li Si", "school" : "BAZHONG", "id": "t2002", "sports": "Track and Field", "clubs": "Chess"}');

> SELECT * FROM student_profiles;

   id   |                                               profile                                                 
+-------+------------------------------------------------------------------------------------------------------+
  d1001 | {"age": "15", "id": "d1001", "name": "Zhang San", "school": "RENDA", "sports": "none"}                
  f3003 | {"age": "15", "clubs": "Basketball", "id": "f3003", "name": "Wang Wu", "school": "SIZHONG"}           
  t2002 | {"clubs": "Chess", "id": "t2002", "name": "Li Si", "school": "BAZHONG", "sports": "Track and Field"}  
(3 rows)

> CREATE TABLE serial (a SERIAL PRIMARY KEY, b STRING, c BOOL);

> SHOW COLUMNS FROM serial;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | INT8      |    false    | unique_rowid() |                       | {primary} |   false    
  b           | STRING    |    true     | NULL           |                       | {}        |   false    
  c           | BOOL      |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO serial (b,c) VALUES ('red', true), ('yellow', false), ('pink', true);

> INSERT INTO serial (a,b,c) VALUES (123, 'white', false);

> SELECT * FROM serial;

          a          |   b    |   c    
+--------------------+--------+-------+
                 123 | white  | false  
  540856466654756865 | red    | true   
  540856466654789633 | yellow | false  
  540856466654822401 | pink   | true   
(4 rows)

> CREATE TABLE strings (a STRING PRIMARY KEY, b STRING(4), c TEXT);

> SHOW COLUMNS FROM strings;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | STRING    |    false    | NULL           |                       | {primary} |   false    
  b           | STRING(4) |    true     | NULL           |                       | {}        |   false    
  c           | STRING    |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO strings VALUES ('a1b2c3d4', 'e5f6', 'g7h8i9');

> SELECT * FROM strings;

     a     |  b   |   c
+----------+------+--------+
  a1b2c3d4 | e5f6 | g7h8i9
(1 row)

> CREATE TABLE aliases (a STRING PRIMARY KEY, b VARCHAR, c CHAR);

> SHOW COLUMNS FROM aliases;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  a           | STRING    |    false    | NULL           |                       | {primary} |   false    
  b           | VARCHAR   |    true     | NULL           |                       | {}        |   false    
  c           | CHAR      |    true     | NULL           |                       | {}        |   false    
(3 rows)

> INSERT INTO aliases VALUES ('abcd1234', 'ef56', 'g');

> SELECT * FROM aliases;

     a     |  b   | c  
+----------+------+---+
  abcd1234 | ef56 | g  
(1 row)

> SELECT length('☃'::string);

  length
+--------+
       1

> SELECT length('☃'::bytes);

  length
+--------+
       3

> CREATE TABLE time (time_id INT PRIMARY KEY, time_val TIME);

> SHOW COLUMNS FROM time;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  time_id     | INT8      |    false    | NULL           |                       | {primary} |   false    
  time_val    | TIME      |    true     | NULL           |                       | {}        |   false    
(2 rows)

> INSERT INTO time VALUES (1, TIME '08:40:00'), (2, TIME '08:41:40');

> SELECT * FROM time;

  time_id |         time_val           
+---------+---------------------------+
        1 | 0000-01-01 08:40:00+00:00  
        2 | 0000-01-01 08:41:40+00:00  
(2 rows)

> SELECT (SELECT time_val FROM time WHERE time_id = 1) < (SELECT time_val FROM time WHERE time_id = 2);

(SELECT time_val FROM "time"  WHERE time_id = 1) < (SELECT  time_val FROM "time" WHERE  time_id = 2)          
+--------------------------------------------------------------------------------------+
              true              
(1 row)

> CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMPTZ);

> SHOW COLUMNS FROM timestamps;

  column_name |  data_type  | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-------------+-------------+----------------+-----------------------+-----------+-----------+
  a           | INT8        |    false    | NULL           |                       | {primary} |   false    
  b           | TIMESTAMPTZ |    true     | NULL           |                       | {}        |   false    
(2 rows)

> INSERT INTO timestamps VALUES (1, TIMESTAMPTZ '2019-03-25 10:10:10-05:00'), (2, TIMESTAMPTZ '2019-03-25');

> SELECT * FROM timestamps;

  a |             b              
+---+---------------------------+
  1 | 2019-03-25 15:10:10+00:00  
  2 | 2019-03-25 00:00:00+00:00  
(2 rows)

> CREATE TABLE v (token uuid);

> INSERT INTO v VALUES ('63616665-6630-3064-6465-616462656562');

> SELECT * FROM v;

                 token                  
+--------------------------------------+
  63616665-6630-3064-6465-616462656562  
(1 row)

> INSERT INTO v VALUES ('{63616665-6630-3064-6465-616462656563}');

> SELECT * FROM v;

                 token                  
+--------------------------------------+
  63616665-6630-3064-6465-616462656562  
  63616665-6630-3064-6465-616462656563  
(2 rows)

> INSERT INTO v VALUES (b'kafef00ddeadbeed');

> SELECT * FROM v;

+--------------------------------------+
  63616665-6630-3064-6465-616462656562  
  63616665-6630-3064-6465-616462656563  
  6b616665-6630-3064-6465-616462656564  
(3 rows)

> INSERT INTO v VALUES ('urn:uuid:63616665-6630-3064-6465-616462656564');

> SELECT * FROM v;

                 token                  
+--------------------------------------+
  63616665-6630-3064-6465-616462656562  
  63616665-6630-3064-6465-616462656563  
  6b616665-6630-3064-6465-616462656564  
  63616665-6630-3064-6465-616462656564  
(4 rows)

> CREATE TABLE users (
        id UUID NOT NULL DEFAULT gen_random_uuid(),
        city STRING NOT NULL,
        name STRING NULL,
        address STRING NULL,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        FAMILY "primary" (id, city, name, address)
);

> INSERT INTO users (name, city) VALUES ('Zhang', 'Beijing'), ('Wang', 'Shanghai'), ('Li', 'Beijing');

> SELECT * FROM users;

                   id                  |   city   | name  | address  
+--------------------------------------+----------+-------+---------+
  5a583e85-a1ce-45bc-a103-ff3490c365e2 | Beijing  | Li    | NULL     
  f61dec54-3fc6-489d-baef-de71bd6509b8 | Beijing  | Zhang | NULL     
  f92c53f1-0992-47f8-9a95-b43d54895f6e | Shanghai | Wang  | NULL     
(3 rows)

> CREATE TABLE users2 (
        id BYTES DEFAULT uuid_v4(),
        city STRING NOT NULL,
        name STRING NULL,
        address STRING NULL,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        FAMILY "primary" (id, city, name, address)
);

> INSERT INTO users2 (name, city) VALUES ('Zhao', 'Beijing'), ('Liu', 'Shanghai'), ('Chen', 'Beijing');

> SELECT * FROM users2;

                           id                          |   city   | name | address  
+------------------------------------------------------+----------+------+---------+
  ~T\021\015'\355G\315\251\270\317\020\304\002\020\345 | Beijing  | Zhao | NULL     
  \354\3331\374\365\364J'\242\305-\000H\2514\011       | Beijing  | Chen | NULL     
  ,`?\233t\235O\361\201\037\353\021\370\321\335G       | Shanghai | Liu  | NULL     
(3 rows)

> CREATE TABLE users3 (
        id INT DEFAULT unique_rowid(),
        city STRING NOT NULL,
        name STRING NULL,
        address STRING NULL,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        FAMILY "primary" (id, city, name, address)
);

> INSERT INTO users3 (name, city) VALUES ('Zhang', 'Beijing'), ('Zhao', 'Shanghai'), ('Li', 'Beijing');

> SELECT * FROM users3;

          id         |   city   | name  | address  
+--------------------+----------+-------+---------+
  541068110770601985 | Beijing  | Zhang | NULL     
  541068110770700289 | Beijing  | Li    | NULL     
  541068110770667521 | Shanghai | Zhao  | NULL     
(3 rows)