建立表

CREATE TABLE IF NOT EXISTS ext_biz_relation_process
(
    id                    SERIAL PRIMARY KEY,
    biz_table             VARCHAR(64) NOT NULL,
    biz_id                integer NOT NULL,
    process_definition_id VARCHAR(64) NOT NULL,
    process_instance_id   VARCHAR(64) NOT NULL,
    process_status        SMALLINT    NOT NULL,
    created_by            integer,
    updated_by            integer,
    deleted_by            integer,
    created_at            timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at            timestamp
    );

COMMENT ON TABLE ext_biz_relation_process IS '业务与流程关联信息表';
COMMENT ON COLUMN ext_biz_relation_process.biz_table IS '业务对应的表名';
COMMENT ON COLUMN ext_biz_relation_process.biz_id IS '业务的ID';
COMMENT ON COLUMN ext_biz_relation_process.process_definition_id IS '流程定义ID';
COMMENT ON COLUMN ext_biz_relation_process.process_instance_id IS '流程实例ID';
COMMENT ON COLUMN ext_biz_relation_process.process_status IS '流程状态（0-未提交 1-运行中 2-已结束）';
COMMENT ON COLUMN ext_biz_relation_process.created_by IS '创建人ID';
COMMENT ON COLUMN ext_biz_relation_process.updated_by IS '更新人ID';
COMMENT ON COLUMN ext_biz_relation_process.deleted_by IS '删除人ID';
COMMENT ON COLUMN ext_biz_relation_process.created_at IS '创建时间';
COMMENT ON COLUMN ext_biz_relation_process.updated_at IS '更新时间';
COMMENT ON COLUMN ext_biz_relation_process.deleted_at IS '删除时间';

建立索引

PostgreSQLIn PostgreSQL, index names must be unique across the entire schema.

PostgreSQL在PostgreSQL中，索引名称在整个模式中必须是唯一的。

CREATE INDEX idx_ebrp_biz_table_id ON ext_biz_relation_process(biz_table, biz_id);
CREATE INDEX idx_ebrp_process_instance_id ON ext_biz_relation_process(process_instance_id);
CREATE INDEX idx_ebrp_process_definition_id ON ext_biz_relation_process(process_definition_id);

删除所有表和索引

-- 关闭外键约束检查：SET session_replication_role = 'replica'; 这行代码会临时关闭外键约束检查，从而避免在删除表时因外键约束而产生错误。
-- 删除所有表：借助 DO 语句和 FOR 循环，查询 pg_tables 系统表以获取 public 模式下的所有表名，然后使用 DROP TABLE 语句将这些表删除。CASCADE 选项可确保在删除表时，与之关联的依赖对象（如视图、触发器等）也会被一并删除。
-- 删除所有索引：同样使用 DO 语句和 FOR 循环，查询 pg_indexes 系统表以获取 public 模式下的所有索引名，再使用 DROP INDEX 语句将这些索引删除。
-- 恢复外键约束检查：SET session_replication_role = 'origin'; 这行代码会恢复外键约束检查。-- 关闭外键约束检查
SET session_replication_role = 'replica';

-- 删除所有表
DO $$
DECLARE
    table_record record;
BEGIN
    FOR table_record IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(table_record.tablename) || ' CASCADE';
    END LOOP;
END $$;

-- 删除所有索引
DO $$
DECLARE
    index_record record;
BEGIN
    FOR index_record IN
        SELECT indexname
        FROM pg_indexes
        WHERE schemaname = 'public'
    LOOP
        EXECUTE 'DROP INDEX IF EXISTS ' || quote_ident(index_record.indexname);
    END LOOP;
END $$;

-- 恢复外键约束检查
SET session_replication_role = 'origin';    

-- 查询全部表
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
-- 查询全部索引
SELECT tablename, indexname FROM pg_indexes WHERE schemaname = 'public';
SELECT * FROM pg_indexes WHERE schemaname = 'public';

表不存储才创建

-- 创建表，如果表不存在
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

索引不存在才创建

-- 创建索引，如果索引不存在
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);    

-- 唯一索引 CREATE INDEX IF NOT EXISTS 语法是从 9.5 版本开始支持的，不过它并不支持创建唯一索引。
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname = 'public'
          AND tablename = 'users'
          AND indexname = 'idx_unique_users_email'
    ) THEN
        CREATE UNIQUE INDEX idx_unique_users_email ON users (email);
    END IF;
END $$;