postgres（pg）数据库使用建表语句创建数据表

?一般创建数据表有两种方式，一种是使用建表语句，二是使用图形化工具建表（如：pgadmin4、Navicat、DataGrip、dbeaver等等之类的工具）；

1、使用建表语句创建数据表：

-- 建立学生测试表语句如下：
-- DROP SEQUENCE IF EXISTS public.test_student_seq;
CREATE SEQUENCE test_student_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- DROP TABLE IF EXISTS public.test_student;
CREATE TABLE IF NOT EXISTS public.test_student
(
    id bigint NOT NULL DEFAULT nextval('test_student_seq'::regclass),
    name character varying(100) COLLATE pg_catalog."default",
    sfzh character varying(20) COLLATE pg_catalog."default",
    phone character varying(12) COLLATE pg_catalog."default",
	sex character varying(10) COLLATE pg_catalog."default",
	age character varying(10) COLLATE pg_catalog."default",
    domicile character varying(255) COLLATE pg_catalog."default",
    birth_date character varying(100) COLLATE pg_catalog."default",
    political_landscape character varying(100) COLLATE pg_catalog."default",
	
    remarks character varying(255) COLLATE pg_catalog."default",
    status character varying(10) COLLATE pg_catalog."default" DEFAULT 0,
    create_time TIMESTAMP without time zone,
    create_by character varying(60) COLLATE pg_catalog."default",
    update_time TIMESTAMP without time zone,
    update_by character varying(60) COLLATE pg_catalog."default",
    res_one character varying(100) COLLATE pg_catalog."default",
    res_two character varying(100) COLLATE pg_catalog."default",
    res_three character varying(100) COLLATE pg_catalog."default",
    res_four character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT test_student_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.test_student    OWNER to postgres;


COMMENT ON TABLE public.test_student    IS '测试学生表';

COMMENT ON COLUMN public.test_student.id    IS 'ID';
COMMENT ON COLUMN public.test_student.name    IS '姓名';
COMMENT ON COLUMN public.test_student.sfzh    IS '身份证号码';
COMMENT ON COLUMN public.test_student.phone    IS '电话号码';
COMMENT ON COLUMN public.test_student.sex    IS '性别';
COMMENT ON COLUMN public.test_student.age    IS '年龄';
COMMENT ON COLUMN public.test_student.domicile    IS '户籍';
COMMENT ON COLUMN public.test_student.birth_date    IS '出生年月';
COMMENT ON COLUMN public.test_student.political_landscape    IS '政治面貌';


COMMENT ON COLUMN public.test_student.remarks    IS '备注';
COMMENT ON COLUMN public.test_student.status    IS '状态';
COMMENT ON COLUMN public.test_student.create_time    IS '创建时间';
COMMENT ON COLUMN public.test_student.create_by    IS '创建人';
COMMENT ON COLUMN public.test_student.update_time    IS '更新时间';
COMMENT ON COLUMN public.test_student.update_by    IS '更新人';
COMMENT ON COLUMN public.test_student.res_one    IS '预留字段一';
COMMENT ON COLUMN public.test_student.res_two    IS '预留字段二';
COMMENT ON COLUMN public.test_student.res_three    IS '预留字段三';
COMMENT ON COLUMN public.test_student.res_four    IS '预留字段四';

2、一般建库之后可以执行如下插件

-- 相关插件
-- 安装postgis插件 解决【没有匹配指定名称和参数类型的函数。您也许需要增加明确的类型转换】
CREATE EXTENSION POSTGIS;

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
-- 加密插件
CREATE EXTENSION pgcrypto;
/**
pgcrypto 扩展模块可以用于 PostgreSQL 中实现加密和解密功能。
从 PostgreSQL 13 版本开始 pgcrypto 属于“可信”模块；只要用户拥有当前数据库上的 CREATE 权限就可以安装该模块，不再需要超级用户权限。
pgcrypto 提供了两类加密算法：单向加密和双向加密。
单向加密属于不可逆加密，无法根据密文解密出明文，适用于数据的验证，例如登录密码验证。
  常用的单向加密算法有 MD5、SHA、HMAC 等。双向加密属于可逆加密，根据密文和密钥可解密出明文，适用于数据的安全传输，例如电子支付、数字签名等。常用的双向加密算法有 AES、DES、RSA、ECC 等。
*/