一般创建数据表有两种方式,一种是使用建表语句,二是使用图形化工具建表(如: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 等。
*/
-- 也可以是
CREATE TABLE IF	NOT EXISTS PUBLIC.table_name(
		ID SERIAL PRIMARY KEY,
		rname VARCHAR ( 100 ),
		id_type VARCHAR ( 20 ),
		id_num VARCHAR ( 20 ),
		sex VARCHAR ( 10 ),
		tel VARCHAR ( 20 ),
		unpay_rea VARCHAR ( 200 ),
		isurge VARCHAR ( 10 ),
		isdel VARCHAR ( 10 ),
		bel_street VARCHAR ( 100 ),
		bel_community VARCHAR ( 100 ),
		verify_name VARCHAR ( 100 ),
		verify_time VARCHAR ( 100 ),
		co_id VARCHAR ( 60 ),
		co_name VARCHAR ( 100 ),
		remarks VARCHAR ( 500 ),
		status VARCHAR ( 10 ) DEFAULT 0,
		create_time TIMESTAMP WITHOUT TIME ZONE,
		create_by VARCHAR ( 60 ),
		update_time TIMESTAMP WITHOUT TIME ZONE,
		update_by VARCHAR ( 60 ),
		res_one VARCHAR ( 100 ),
		res_two VARCHAR ( 100 ),
		res_three VARCHAR ( 100 ),
		res_four VARCHAR ( 100 ) 
	);
COMMENT ON COLUMN "public"."table_name"."id" IS 'ID';

Logo

腾讯云面向开发者汇聚海量精品云计算使用和开发经验,营造开放的云计算技术生态圈。

更多推荐