postgres的一些数据类型

数据类型

TEXT

在mysql中，储存长文本，你需要在以下类型中做出选择。

TEXT can store 65,535 characters (approx 64KB)
MEDIUMTEXT = 16,777,215 characters (approx 16 MB) 
LONGTEXT = 4,294,967,295 chars (approx 4GB)

这是pg的，长文本一律使用TEXT，无限长度

Character Types	Description
CHARACTER VARYING(n), VARCHAR(n)	variable-length with length limit
CHARACTER(n), CHAR(n)	fixed-length, blank padded
TEXT, VARCHAR	variable unlimited length

learn=# CREATE TABLE text_test (
id serial PRIMARY KEY,
content text);
CREATE TABLE
learn=# INSERT INTO text_test (content)
SELECT repeat(md5(random()::TEXT), 1000000)
    FROM generate_series(1, 5) x;
INSERT 0 5
learn=# select right(content, 100) from text_test WHERE id = 5;
                                                right                                                 
------------------------------------------------------------------------------------------------------
 c521f503fe21d2c632538c6342343767c521f503fe21d2c632538c6342343767c521f503fe21d2c632538c6342343767c521
(1 row)

learn=# select length(content) from text_test;
  length  
----------
 32000000
 32000000
 32000000
 32000000
 32000000
(5 rows)

这里，循环5次，每次向表中插入一行。其中每行的content文本长度都是32,000,000。

mysql utf8的坑

MySQL中的utf8实质上不是标准的UTF8。到处都有mysql的这个坑的文章blog.liexing.me/2018/07/27/… 而postgres没这个坑。

learn=# CREATE TABLE test_user (
learn(# id SERIAL primary key,
learn(# name text not null
learn(# );
CREATE TABLE
learn=# INSERT INTO test_user(name) VALUES('我是😁');
INSERT 0 1
learn=# select * from test_user;
 id |  name
----+--------
  1 | 我是😁
(1 row)

timestamp

pg中timestamp的大小高达8 bytes。支持的时间范围是公元前 4713到公元 294276 年。（所以没有MYSQL timestamp 只能达到2038年的坑）

learn=# CREATE TABLE ts_test (
learn(# id serial primary key,
learn(# ts timestamp);
CREATE TABLE
learn=# INSERT INTO ts_test (ts) (SELECT NOW());
INSERT 0 1
learn=# INSERT INTO ts_test (ts)VALUES ('10000-01-01 00:00:00');
INSERT 0 1
learn=# select * from ts_test;
 id |             ts             
----+----------------------------
  1 | 2023-04-09 20:09:34.811747
  2 | 10000-01-01 00:00:00
(2 rows)

learn=# 

这里顺便提一下。在web服务中以无时区的时间戳来代替使用日期时间，应该是更好的（我还没实践过）。 在我看来，至少有以下优势：

1. 前后端互相发送数据时，不用考虑时区相关的问题。只有给用户看的时候，才需要转为文本。
2. 解析时间变得很简单。日期时间的格式化有很多种格式。前几天我开发遇到一个问题，前端向后端发送数据的时候，因为格式不一致而解析失败，需要先转换日期时间字符串的格式。而时间戳格式很少。一个项目里很容易统一，比如都用64位无时区时间戳。

uuid

mysql虽然支持生成uuid，但是没有这个类型。官方文档的示例中，使用BINARY(16)来储存uuid。pg里有这种类型。

learn=# select gen_random_uuid();
           gen_random_uuid            
--------------------------------------
 a8e62791-d04d-403b-b8ab-88b7d0e1d69d
(1 row)
learn=# CREATE TABLE uuid_test (
learn(# id uuid DEFAULT uuid_generate_v4 (),
learn(# name text);
CREATE TABLE
learn=# INSERT INTO uuid_test (name ) VALUES ('Tom'), ('Jane');
INSERT 0 2
learn=# select * from uuid_test ;
                  id                  | name 
--------------------------------------+------
 c7324b81-91b3-44a6-90bd-0ef629ff2cb3 | Tom
 27d4c4da-5f35-4ee2-8270-3dc39218457f | Jane
(2 rows)

learn=# INSERT INTO uuid_test VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Mike');
INSERT 0 1
learn=# INSERT INTO uuid_test VALUES ('dummy', 'Alice');
ERROR:  invalid input syntax for type uuid: "dummy"
LINE 1: INSERT INTO uuid_test VALUES ('dummy', 'Alice');
                                      ^
learn=# 

array

在一般的关系型数据库中，表示一对多的关系，，一般用两个表，也就是就是A表中的一行对应B表中的多行。 例如一个人有多个电话号码，需要这样表示。

CREATE TABLE person ( id INT PRIMARY KEY, name VARCHAR(100) ); 
CREATE TABLE phone ( id INT PRIMARY KEY, person_id INT, number VARCHAR(20), FOREIGN KEY (person_id) REFERENCES person(id) ); 

而在pg中，对于极其简单的一对多，可以这样表示

learn=# CREATE TABLE contacts (
        id serial PRIMARY KEY,
        name VARCHAR (100),
        phones TEXT []
);
CREATE TABLE
learn=# INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);
INSERT 0 1
learn=# INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-58423"}');
INSERT 0 2
learn=# SELECT
        name,
        phones
FROM
        contacts;
     name     |              phones              
--------------+----------------------------------
 John Doe     | {(408)-589-5846,(408)-589-5555}
 Lily Bush    | {(408)-589-5841}
 William Gate | {(408)-589-5842,(408)-589-58423}
(3 rows)
learn=# SELECT
        name,
        phones
FROM
        contacts
WHERE
        '(408)-589-5555' = ANY (phones);
   name   |             phones              
----------+---------------------------------
 John Doe | {(408)-589-5846,(408)-589-5555}
(1 row)

自定义类型

有点像c语言中的结构体。

learn=# CREATE TYPE person AS (
    id INT,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);
CREATE TYPE
learn=# CREATE TABLE type_test (
    id serial PRIMARY KEY,
    person_info person
);
CREATE TABLE
learn=# INSERT INTO type_test (person_info)
VALUES ((1, 'John Doe', 30, 'john.doe@example.com'));
INSERT 0 1
learn=# select * from type_test;
 id |              person_info               
----+----------------------------------------
  1 | (1,"John Doe",30,john.doe@example.com)
(1 row)

learn=#