PostgreSQL|PostgreSQL 的字段类型和表操作笔记
字段类型 数值类型
Name | Storage Size | Description | Range |
---|---|---|---|
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807 |
decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
Name | Storage Size | Description | Range |
---|---|---|---|
money | 8 bytes | currency amount | -92233720368547758.08 to +92233720368547758.07 |
SELECT '12.34'::float8::numeric::money;
money 可以无损转换为 numeric, 转换为其他类型则会有精度损失, 例如
SELECT '52093.89'::money::numeric::float8;
字符串类型
Name | Description |
---|---|
character varying(n), varchar(n) | variable-length with limit |
character(n), char(n) | fixed-length, blank padded |
text | variable unlimited length |
Name | Storage Size | Description |
---|---|---|
bytea | 1 or 4 bytes plus the actual binary string | variable-length binary string |
\x sequence
SELECT '\xDEADBEEF';
时间类型
Name | Storage Size | Description | Low Value | High Value | Resolution |
---|---|---|---|---|---|
timestamp [ (p) ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ (p) ] with time zone | 12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
布尔类型
Name | Storage Size | Description |
---|---|---|
boolean | 1 byte | state of true or false |
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
使用
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe| happy
(1 row)
排序和比较
INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
name| current_mood
-------+--------------
Moe| happy
Curly | ok
(2 rows)SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
name| current_mood
-------+--------------
Curly | ok
Moe| happy
(2 rows)SELECT name
FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
name
-------
Larry
(1 row)
地理位置类型
Name | Storage Size | Description | Representation |
---|---|---|---|
point | 16 bytes | Point on a plane | (x,y) |
line | 32 bytes | Infinite line | {A,B,C} |
lseg | 32 bytes | Finite line segment | ((x1,y1),(x2,y2)) |
box | 32 bytes | Rectangular box | ((x1,y1),(x2,y2)) |
path | 16+16n bytes | Closed path (similar to polygon) | ((x1,y1),...) |
path | 16+16n bytes | Open path | [(x1,y1),...] |
polygon | 40+16n bytes | Polygon (similar to closed path) | ((x1,y1),...) |
circle | 24 bytes | Circle | <(x,y),r> (center point and radius) |
Name | Storage Size | Description |
---|---|---|
cidr | 7 or 19 bytes | IPv4 and IPv6 networks |
inet | 7 or 19 bytes | IPv4 and IPv6 hosts and networks |
macaddr | 6 bytes | MAC addresses |
macaddr8 | 8 bytes | MAC addresses (EUI-64 format) |
二者最关键的区别在于, inet 允许IP地址在掩码区域外有非零值, 例如 "192.168.0.1/24", 这个值对于 cidr 是不允许的.
如果不喜欢 inet 或 cidr 输出的格式, 可以使用 host, text 和 abbrev 这些函数进行处理.
二进制串类型 使用0和1表示的字符串, sql示例
CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');
ERROR:bit string length 2 does not match type bit(3)INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;
a|b
-----+-----
101 | 00
100 | 101
文本搜索类型 PostgreSQL provides two data types that are designed to support full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query. The tsvector type represents a document in a form optimized for text search; the tsquery type similarly represents a text query. Chapter 12 provides a detailed explanation of this facility, and Section 9.13 summarizes the related functions and operators.
tsvector, tsquery
UUID类型 字段长16 byte(128-bit), 用于分布式系统可以提供更好的唯一性保证(相对于自增序列). 一个 UUID 是一组短横线分隔的十六进制小写数字,
格式为: 一组8位, 三组4位, 最后是一组12位, 一共32位组成128bit. 例如
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
XML类型
XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT 'Manual - 锐客网 ... ')
XMLPARSE (CONTENT 'abcbar foo ')XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )
JSON类型 数组类型
CREATE TABLE sal_emp (
nametext,
pay_by_quarterinteger[],
scheduletext[][]
);
CREATE TABLE tictactoe (
squaresinteger[3][3]
);
增和查
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
The result of the previous two inserts looks like this:SELECT * FROM sal_emp;
name|pay_by_quarter|schedule
-------+---------------------------+-------------------------------------------
Bill| {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)-- 使用 ARRAY
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)
改
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
-- 使用 ARRAY
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
自定义类型, 组合类型
CREATE TYPE complex AS (
rdouble precision,
idouble precision
);
CREATE TYPE inventory_item AS (
nametext,
supplier_idinteger,
pricenumeric
);
CREATE TABLE on_hand (
iteminventory_item,
countinteger
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;
SELECT price_extension(item, 10) FROM on_hand;
CREATE TABLE inventory_item (
nametext,
supplier_idinteger REFERENCES suppliers,
pricenumeric CHECK (price > 0)
);
Table相关SQL 建表 CREATE TABLE 建表说明: https://www.postgresql.org/docs/14/sql-createtable.html
完整的建表语法
CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]-- column_constraint 字段约束的格式
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]-- table_constraint 表约束的格式
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
还有
OF type_name
和PARTITION OF parent_table
两种, 比较少用.TEMPORARY | TEMP
临时表, 在session结束后自动drop
UNLOGGED
对UNLOGGED表的写入不记入 write-ahead 日志, 所以比普通表快. 如果数据库崩溃(crash)或非常关机, UNLOGGED表会被自动truncate. UNLOGGED表不能replicated, 基于UNLOGGED表的索引也会是UNLOGGED的.
COMPRESSION
压缩仅用于变长字段类型, and is used only when the column's storage mode is main or extended
PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ opclass ] [, ...] )
【PostgreSQL|PostgreSQL 的字段类型和表操作笔记】用于对表进行分区. The table thus created is called a partitioned table. The parenthesized list of columns or expressions forms the partition key for the table. When using range or hash partitioning, the partition key can include multiple columns or expressions (up to 32, but this limit can be altered when building PostgreSQL), but for list partitioning, the partition key must consist of a single column or expression.
Range and list partitioning require a btree operator class, while hash partitioning requires a hash operator class. If no operator class is specified explicitly, the default operator class of the appropriate type will be used; if no default operator class exists, an error will be raised. When hash partitioning is used, the operator class used must implement support function 2 (see Section 38.16.3 for details).
表分区后, 会变成一系列子表, 原表本身变成空表. 向原表的写入, 会路由到对应的子表, 如果对应的分区不存在就会报错. 分区表不支持 EXCLUDE 约束, 但是在子表中可以定义.
NOT NULL, NULL, DEFAULT, UNIQUE, PRIMARY KEY
和MySQL用法一样
GENERATED ALWAYS AS ( generation_expr ) STORED
类似于view, 这种字段由其他字段(非generated)生成, 不能写只能读
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
表示此字段为ID字段, 使用一个绑定的sequence自动赋值, 并且这个字段一定是NOT NULL. This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it. Such a column is implicitly NOT NULL.
The clauses ALWAYS and BY DEFAULT determine how explicitly user-specified values are handled in INSERT and UPDATE commands.
TABLESPACE tablespace_name
表空间, 未指定则使用 default_tablespace, 如果是临时表, 则使用 temp_tablespaces.
建表示例
设置主键
CREATE TABLE films (
codechar(5) CONSTRAINT firstkey PRIMARY KEY,
titlevarchar(40) NOT NULL,
didinteger NOT NULL,
date_proddate,
kindvarchar(10),
leninterval hour to minute
);
CREATE TABLE distributors (
didinteger PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
namevarchar(40) NOT NULL CHECK (name <> '')
);
CREATE TABLE films (
codechar(5),
titlevarchar(40),
didinteger,
date_proddate,
kindvarchar(10),
leninterval hour to minute,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
-- 下面两个是等价的
CREATE TABLE distributors (
didinteger,
namevarchar(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
didinteger PRIMARY KEY,
namevarchar(40)
);
二维数组字段
CREATE TABLE array_int (
vectorint[][]
);
唯一约束字段
CREATE TABLE films (
codechar(5),
titlevarchar(40),
didinteger,
date_proddate,
kindvarchar(10),
leninterval hour to minute,
CONSTRAINT production UNIQUE(date_prod)
);
CREATE TABLE distributors (
didinteger,
namevarchar(40) UNIQUE
);
表达式约束字段
CREATE TABLE distributors (
didinteger,
namevarchar(40),
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
设置字段默认值
CREATE TABLE distributors (
namevarchar(40) DEFAULT 'Luso Films',
didinteger DEFAULT nextval('distributors_serial'),
modtimetimestamp DEFAULT current_timestamp
);
非空约束
CREATE TABLE distributors (
didinteger CONSTRAINT no_null NOT NULL,
namevarchar(40) NOT NULL
);
对表进行分区
CREATE TABLE measurement (
logdatedate not null,
peaktempint,
unitsalesint
) PARTITION BY RANGE (logdate);
-- 分区依据多个字段
CREATE TABLE measurement_year_month (
logdatedate not null,
peaktempint,
unitsalesint
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
-- 使用list分区
CREATE TABLE cities (
city_idbigserial not null,
nametext not null,
populationbigint
) PARTITION BY LIST (left(lower(name), 1));
-- 使用hash分区
CREATE TABLE orders (
order_idbigint not null,
cust_idbigint not null,
statustext
) PARTITION BY HASH (order_id);
-- 使用区间分区
CREATE TABLE measurement_y2016m07
PARTITION OF measurement (
unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
分别创建 表measurement_year_month 的各个分区子表
CREATE TABLE measurement_ym_older
PARTITION OF measurement_year_month
FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);
CREATE TABLE measurement_ym_y2016m11
PARTITION OF measurement_year_month
FOR VALUES FROM (2016, 11) TO (2016, 12);
CREATE TABLE measurement_ym_y2016m12
PARTITION OF measurement_year_month
FOR VALUES FROM (2016, 12) TO (2017, 01);
CREATE TABLE measurement_ym_y2017m01
PARTITION OF measurement_year_month
FOR VALUES FROM (2017, 01) TO (2017, 02);
或者
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
对于以上的分区方式, 可以设置一个默认子分区
CREATE TABLE cities_partdef
PARTITION OF cities DEFAULT;
推荐阅读
- 《破碎的残阳,我们逆光》连载小说-|《破碎的残阳,我们逆光》连载小说- HashMap剖析
- 怎么给字符串字段加索引()
- VS|VS code 配置R的一些坑(Mac)
- Python|Python 爬取 "王者荣耀.英雄壁纸" 过程中的矛和盾
- 简单强大的断言库|简单强大的断言库 power-assert
- 新版MDN正式上线,还有收费版的MDN Plus,下个月也即将到来
- 如何有更好的职业发展
- 不寻常的 Java(StackTrace 扩展了 Throwable)
- 6.对象的实例化内存布局与访问定位
- druid连接池引起的线程blocked