1.创建表的时候定义列级约束

create table zyf_col_con(
       id bigint primary key, 
       name varchar(50) not null,
       age  int
);

insert into zyf_col_con values(1,'zyf',30);

zzzdb=> insert into zyf_col_con values(1,'zyf',30);
ERROR:  duplicate key value violates unique constraint "zyf_col_con_pkey"
DETAIL:  Key (id)=(1) already exists.
zzzdb=> \d zyf_col_con
         Table "public.zyf_col_con"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               |
Indexes:
    "zyf_col_con_pkey" PRIMARY KEY, btree (id) TABLESPACE enmtbs

zzzdb=>

2 创建表的时候定义表级约束

create table zyf_tab_con(
      id bigint,
      name varchar(50) not null,   
      age  int,
      primary key(id) -- 创建表级约束
     );

insert into zyf_tab_con values(1,'zyf_tab',30);

zzzdb=> insert into zyf_tab_con values(1,'zyf_tab',30);
INSERT 0 1
zzzdb=> insert into zyf_tab_con values(1,'zyf_tab',30);
ERROR:  duplicate key value violates unique constraint "zyf_tab_con_pkey"
DETAIL:  Key (id)=(1) already exists.
zzzdb=> \d zyf_tab_con
         Table "public.zyf_tab_con"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               |
Indexes:
    "zyf_tab_con_pkey" PRIMARY KEY, btree (id) TABLESPACE enmtbs

zzzdb=>

3 为表的属性定义默认值

create table zyf_col_default(
      id bigint,
      name varchar(50) not null default 'zyftest',   
      age  int,
      primary key(id) -- 创建表级约束
     );

zzzdb=> \d zyf_col_default;
                         Table "public.zyf_col_default"
 Column |         Type          |                   Modifiers
--------+-----------------------+-----------------------------------------------
 id     | bigint                | not null
 name   | character varying(50) | not null default 'zyftest'::character varying
 age    | integer               |
Indexes:
    "zyf_col_default_pkey" PRIMARY KEY, btree (id) TABLESPACE enmtbs

zzzdb=> insert into zyf_col_default(id ,age) values(1,10);
INSERT 0 1
zzzdb=> insert into zyf_col_default(id ,age) values(2,20);
INSERT 0 1
zzzdb=> insert into zyf_col_default values(3,'zzzz',30);
INSERT 0 1


zzzdb=> select *from zyf_col_default;
 id |  name   | age
----+---------+-----
  1 | zyftest |  10
  2 | zyftest |  20
  3 | zzzz    |  30
(3 rows)

zzzdb=>

4 如果在创建表的时候，没有为某列定义默认值，缺省的默认值是空值null

create table zyf_col_null_default(
      id bigint,
      name varchar(50) ,   
      age  int,
      primary key(id) -- 创建表级约束
     );

zzdb=> \d zyf_col_null_default
    Table "public.zyf_col_null_default"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) |
 age    | integer               |
Indexes:
    "zyf_col_null_default_pkey" PRIMARY KEY, btree (id) TABLESPACE enmtbs
zzzdb=>
zzzdb=>
zzzdb=> insert into zyf_col_null_default(id) values(1);
INSERT 0 1
zzzdb=> insert into zyf_col_null_default(id,name ) values(2,'abc');
INSERT 0 1
zzzdb=> insert into zyf_col_null_default(id,name,age ) values(3,'zzz',19);
INSERT 0 1
zzzdb=> select * from zyf_col_null_default;
 id | name | age
----+------+-----
  1 |      |
  2 | abc  |
  3 | zzz  |  19
(3 rows)

5.创建表时使用自增数据类型

create table t_seq(id  serial NOT NULL,name varchar(20));

zzzdb=> \d t_seq
                                Table "public.t_seq"
 Column |         Type          |                     Modifiers
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null default nextval('t_seq_id_seq'::regclass)
 name   | character varying(20) |


zzzdb=> insert into t_seq (name ) values('a');
INSERT 0 1
zzzdb=> select *from t_seq;
 id | name
----+------
  1 | a
(1 row)

zzzdb=> insert into t_seq values(1,'aa');
INSERT 0 1
zzzdb=> select *from t_seq;
 id | name
----+------
  1 | a
  1 | aa
(2 rows)

zzzdb=> insert into t_seq values(2,'bb');
INSERT 0 1
zzzdb=> insert into t_seq (name ) values('ccc');
INSERT 0 1
zzzdb=> select * from t_seq;
 id | name
----+------
  1 | a
  1 | aa
  2 | bb
  2 | ccc
(4 rows)

### 给表创建主键看看

create table t_seq_pri(
       id serial primary key, 
       name varchar(50) not null,
       age  int
);
NOTICE:  CREATE TABLE will create implicit sequence "t_seq_pri_id_seq" for serial column "t_seq_pri.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_seq_pri_pkey" for table "t_seq_pri"
CREATE TABLE

zzzdb=> insert into t_seq_pri (name ,age) values('a',10);
INSERT 0 1
zzzdb=> insert into t_seq_pri (id, name ,age) values(2,'bbb',20);
INSERT 0 1
zzzdb=> insert into t_seq_pri (name ,age) values('ccc',30);
ERROR:  duplicate key value violates unique constraint "t_seq_pri_pkey"
DETAIL:  Key (id)=(2) already exists.
zzzdb=> insert into t_seq_pri (name ,age) values('ccc',30);
INSERT 0 1
zzzdb=> select *from t_seq_pri;
 id | name | age
----+------+-----
  1 | a    |  10
  2 | bbb  |  20
  3 | ccc  |  30
(3 rows)

2.5 使用现有的表创建新表

                                             ^
zzzdb=> create table t_tab as   select * from pg_tables;
INSERT 0 142
zzzdb=> \d t_tab
                 Table "public.t_tab"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 schemaname    | name                     |
 tablename     | name                     |
 tableowner    | name                     |
 tablespace    | name                     |
 hasindexes    | boolean                  |
 hasrules      | boolean                  |
 hastriggers   | boolean                  |
 tablecreator  | name                     |
 created       | timestamp with time zone |
 last_ddl_time | timestamp with time zone |

zzzdb=> \d  pg_tables
             View "pg_catalog.pg_tables"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 schemaname    | name                     |
 tablename     | name                     |
 tableowner    | name                     |
 tablespace    | name                     |
 hasindexes    | boolean                  |
 hasrules      | boolean                  |
 hastriggers   | boolean                  |
 tablecreator  | name                     |
 created       | timestamp with time zone |
 last_ddl_time | timestamp with time zone |

zzzdb=>