postgresql DDL操作

添加列
新加的字段为默认值，如果没有默认值，则会自动默认为空

mydb=# select * from products;
 product_no | name | price
------------+------+-------
          1 | aaa  |  2.11
          1 | aa   |
(2 rows)

mydb=# ALTER TABLE products ADD COLUMN description text;
ALTER TABLE
mydb=# select * from products;
 product_no | name | price | description
------------+------+-------+-------------
          1 | aaa  |  2.11 |
          1 | aa   |       |
(2 rows)

删除列

mydb=# ALTER TABLE products DROP COLUMN description;
ALTER TABLE
mydb=# select * from products;
 product_no | name | price
------------+------+-------
          1 | aaa  |  2.11
          1 | aa   |
(2 rows)

mydb=#

添加列并添加约束
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

添加表级约束

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

alter table tablename add check/CONSTRAINT 约束名 UNIQUE/foreign key...

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;//该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束


ALTER TABLE products ALTER COLUMN name SET NOT NULL;

mydb=# select * from products
mydb-# ;
 product_no | name | price
------------+------+-------
          1 | aaa  |  2.11
          1 | aa   |
(2 rows)

mydb=# update products set name='' where name='aaa';
UPDATE 1
mydb=# select * from products
;
 product_no | name | price
------------+------+-------
          1 | aa   |
          1 |      |  2.11
(2 rows)

mydb=# ALTER TABLE products ADD CHECK (name <> '');
ERROR:  check constraint "products_name_check" is violated by some row //表级约束添加会检查已存在的数据

mydb=# ALTER TABLE products ALTER COLUMN name SET NOT NULL; //只对未来插入的数据生效
ALTER TABLE
mydb=# \d+ products
                                    Table "public.products"
   Column   |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
------------+---------+-----------+----------+---------+----------+--------------+-------------
 product_no | integer |           |          |         | plain    |              |
 name       | text    |           | not null |         | extended |              |
 price      | numeric |           |          |         | main     |              |
Check constraints:
    "positive_price" CHECK (price > 0::numeric)

mydb=#

mydb=# \d+ products
                                    Table "public.products"
   Column   |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
------------+---------+-----------+----------+---------+----------+--------------+-------------
 product_no | integer |           |          |         | plain    |              |
 name       | text    |           | not null |         | extended |              |
 price      | numeric |           |          |         | main     |              |
Check constraints:
    "positive_price" CHECK (price > 0::numeric)

mydb=# insert into products values(2,'',3.0);
INSERT 0 1
mydb=# insert into products values(2,null,3.0);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (2, null, 3.0).
mydb=#

删除表级约束
ALTER TABLE products DROP CONSTRAINT some_name;

删除单列的约束

mydb=# ALTER TABLE products ALTER COLUMN name DROP NOT NULL;
ALTER TABLE
mydb=# \d+ products
                                    Table "public.products"
   Column   |  Type   | Collation | Nullable | Defau

lt | Storage | Stats target | Description
------------+---------+-----------+----------+---------+----------+--------------+-------------
product_no | integer | | | | plain | |
name | text | | | | extended | |
price | numeric | | | | main | |
Check constraints:
"positive_price" CHECK (price > 0::numeric)

更改表中的默认值
ALTER TABLE products ALTER COLUMN price SET DEFAULT xxx;
删除默认值
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
修改列的数据类型
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

重命名列

ALTER TABLE products RENAME COLUMN name TO product_name;
mydb=# ALTER TABLE products RENAME COLUMN name TO product_name;
ALTER TABLE
mydb=# \d+ products
                                     Table "public.products"
    Column    |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+---------+-----------+----------+---------+----------+--------------+-------------
 product_no   | integer |           |          |         | plain    |              |
 product_name | text    |           |          |         | extended |              |
 price        | numeric |           |          |         | main     |              |
Check constraints:
    "positive_price" CHECK (price > 0::numeric)

重命名表名

mydb=# ALTER TABLE products RENAME TO product;
ALTER TABLE
mydb=# \d+ product;
                                     Table "public.product"
    Column    |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+---------+-----------+----------+---------+----------+--------------+-------------
 product_no   | integer |           |          |         | plain    |              |
 product_name | text    |           |          |         | extended |              |
 price        | numeric |           |          |         | main     |              |
Check constraints:
    "positive_price" CHECK (price > 0::numeric)

mydb=#