postgresql12-create table

原创文成 2020-08-04

1096

前一章，创建了一个hr数据库,接下来需要创建一个关系型数据库表(emp)。

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 hr        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres-# \c hr
You are now connected to database "hr" as user "postgres".
hr-# \d
Did not find any relations.
hr=# create table emp(emp_id int,emp_name varchar(30),primary key(emp_id));
CREATE TABLE
hr=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)
复制

这样，我们就在hr下创建了一个emp表。
在postgresql12中创建表的语法如下：

hr=# \help create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ 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 ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ 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 ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ 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 ]

where column_constraint is:

[ 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 ]

and table_constraint is:

[ 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 ]

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

URL: https://www.postgresql.org/docs/12/sql-createtable.html
复制

当创建的表是临时表的时候，可以根据 on commit选项规定事务提交后对临时表的操作，主要定义以下三种行为

PRESERVE ROWS
默认的行为，事务提交后不删除数据，也不删除表

DELETE ROWS
所有的数据在事务结束后被删除，类似执行了 truncate操作

DROP
事务结束后，表连同数据都被删除
复制

postgresql12支持的内置数据类型如下：

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit [ (n) ]		fixed-length bit string
bit varying [ (n) ]	varbit [ (n) ]	variable-length bit string
boolean	bool	logical Boolean (true/false)
box		rectangular box on a plane
bytea		binary data (“byte array”)
character [ (n) ]	char [ (n) ]	fixed-length character string
character varying [ (n) ]	varchar [ (n) ]	variable-length character string
cidr		IPv4 or IPv6 network address
circle		circle on a plane
date		calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
inet		IPv4 or IPv6 host address
integer	int,int4	signed four-byte integer
interval [?fields?] [ § ]		time span
json		textual JSON data
jsonb		binary JSON data, decomposed
line		infinite line on a plane
lseg		line segment on a plane
macaddr		MAC (Media Access Control) address
macaddr8		MAC (Media Access Control) address (EUI-64 format)
money		currency amount
numeric [ (p,s) ]	decimal [ (p,s) ]	exact numeric of selectable precision
path		geometric path on a plane
pg_lsn		PostgreSQL Log Sequence Number
point		geometric point on a plane
polygon		closed geometric path on a plane
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	autoincrementing two-byte integer
serial	serial4	autoincrementing four-byte integer
text		variable-length character string
time [ p ] [ without time zone ]		time of day (no time zone)
time [ p ] with time zone	timetz	time of day, including time zone
timestamp [ p ] [ without time zone ]		date and time (no time zone)
timestamp [ p ] with time zone	timestamptz	date and time, including time zone
tsquery		text search query
tsvector		text search document
txid_snapshot		user-level transaction ID snapshot
uuid		universally unique identifier
xml		XML data

postgresql12也支持表分区功能，分区策略为 RANGE | LIST | HASH 三种

hr=# CREATE TABLE emp_p (
    emp_id         INT NOT NULL ,
    emp_name VARCHAR(30),
    hire_date         DATE NOT NULL
) PARTITION BY RANGE (hire_date);

CREATE INDEX ON emp_p(hire_date);

CREATE TABLE emp_p_2020 PARTITION OF emp_p
    FOR VALUES FROM ('2020-01-01') TO ('2020-12-31');

CREATE TABLE emp_p_2021 PARTITION OF emp_p
    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');
hr=# \d
                 List of relations
 Schema |    Name    |       Type        |  Owner   
--------+------------+-------------------+----------
 public | emp        | table             | postgres
 public | emp_p      | partitioned table | postgres
 public | emp_p_2020 | table             | postgres
 public | emp_p_2021 | table             | postgres
(4 rows)

hr=# select * from emp_p;
 emp_id | emp_name | hire_date 
--------+----------+-----------
(0 rows)

hr=# insert into emp_p values(1,'tom','2020-05-04');
INSERT 0 1
hr=# select * from emp_p;
 emp_id | emp_name | hire_date  
--------+----------+------------
      1 | tom      | 2020-05-04
(1 row)

hr=# select * from emp_p_2021;
 emp_id | emp_name | hire_date 
--------+----------+-----------
(0 rows)

hr=# select * from emp_p_2020;
 emp_id | emp_name | hire_date  
--------+----------+------------
      1 | tom      | 2020-05-04
(1 row)

hr=# insert into emp_p values(1,'tom','2019-05-04');
ERROR:  no partition of relation "emp_p" found for row
DETAIL:  Partition key of the failing row contains (hire_date) = (2019-05-04).
hr=# \d emp_p
                  Partitioned table "public.emp_p"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 emp_id    | integer               |           | not null | 
 emp_name  | character varying(30) |           |          | 
 hire_date | date                  |           | not null | 
Partition key: RANGE (hire_date)
Indexes:
    "emp_p_hire_date_idx" btree (hire_date)
Number of partitions: 2 (Use \d+ to list them.)

hr=# \d emp_p_2020
                     Table "public.emp_p_2020"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 emp_id    | integer               |           | not null | 
 emp_name  | character varying(30) |           |          | 
 hire_date | date                  |           | not null | 
Partition of: emp_p FOR VALUES FROM ('2020-01-01') TO ('2020-12-31')
Indexes:
    "emp_p_2020_hire_date_idx" btree (hire_date)
复制

创建表的其他语法跟其他关系型数据库都差不多。

postgresql