自增序列创建方式 1:CREATE TABLE public.table1 ( id bigserial NOT NULL, status varchar(3), creator varchar(50), create_date timestamptz(6), modifier varchar(50), modify_date timestamp(6) ); tip: 使用 serial 或者 bigserial,数据库会自动创建对应的索引 table1_id_seq ALTER TABLE table2 ADD COLUMN id BIGSERIAL;自增序列创建方式 2:CREATE SEQUENCE public. table1_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 21 CACHE 1; CREATE TABLE public.table1 ( id int4 NOT NULL DEFAULT nextval(table1_id_seq::regclass), status varchar(3) COLLATE pg_catalog.default, creator varchar(50) COLLATE pg_catalog.default, create_date timestamptz(6), modifier varchar(50) COLLATE pg_catalog.default, modify_date timestamp(6) );PostgreSQL 自增序列从 1 开始alter sequence table1_id_seq restart with 1;PostgreSQL 高效清空表数据truncatetable table1;tip:truncate属于DDL操作,不记录行的删除操作,通常不可回滚,所以更快PostgreSQL 查询表所有字段SELECT column_name FROM information_schema.columns WHERE table_schema public AND table_name 表名字; // 行转换列展示 SELECT string_agg(column_name,,) FROM information_schema.columns WHERE table_schema public AND table_name 表名字;PostgreSQL 查看数据库活跃进程SELECT pid, usename, datname, query, state, query_start FROM pg_stat_activity where datname 数据库名称 and state active order by query_start descPostgreSQL 查看 Kill 进程// 只能 kill select 查询updae delete DML不生效 select pg_cancel_backend(pid); // 可以 kill 各种DML (CRUD)操作 select pg_terminate_backend(pid);PostgreSQL upsert 方式更新数据on conflict (part_number) 是business_table 唯一性的更新条件并且在 business_table 表中 part_number 字段一定要是主键索引. 不然会报错!insert into business_table ( version_number,part_number,category,create_date ) select version_number,number,category,now() from inbound_table on conflict (part_number) do update set version_number excluded.version_number, categoryexcluded.category, part_numberexcluded.part_number, categoryexcluded.category, create_date excluded.create_date