PostgreSQL常用sql语句

缘由: 项目使用的是PostgreSQL，时常需要变更字段的名称/限制/类型

1. 创建新表test

CREATE TABLE test (id int8 generated by default as identity, created_time TIMESTAMP not null, updated_time TIMESTAMP, version integer, trade_id integer, name varchar(64) not null, code varchar(64) not null unique, is_active boolean, minimum numeric(30, 8), maximum numeric(30, 8), price decimal(30,8), interval integer, primary key (id));

2. 在已有的test表中增加字段text，字段类型为varchar(64)

ALTER TABLE test ADD text varchar(64);

3. 根据test的name，code，text三个字段做联合唯一键，三个字段值都一样才触发唯一约束

ALTER TABLE test add constraint unique_name_code_text unique("name", "code", "text");

4. 更改trade_id的字段类型为int8

ALTER TABLE test ALTER COLUMN trade_id TYPE int8;

5. 设置trade_id字段不为NULL

ALTER TABLE test ALTER COLUMN trade_id SET NOT NULL;

6. 将code字段值为null的记录批量更新code字段值为"test_code"

UPDATE test set code = 'test_code' WHERE code is null;

7. 根据id删除记录

DELETE FROM test WHERE ID IN (83,84,85);

8. 将test表的name字段值全部更新为"test_name"

UPDATE test SET name = 'test_name';

Computer science and software engineering have always been my passion