PostgreSQL相关备忘(一)

常用命令

-- 删表
DROP TABLE IF EXISTS "user_info";
-- 建表
CREATE TABLE "user_info" (
	"id" BIGINT NOT NULL,
	"username" VARCHAR ( 50 ) NOT NULL,
	"birthday" DATE DEFAULT NULL,
	"age" INT DEFAULT NULL,
	"sex" SMALLINT DEFAULT NULL,
	"create_by" BIGINT NOT NULL,
	"create_time" TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"update_by" BIGINT NOT NULL,
	"update_time" TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP 
);
-- 添加注释
COMMENT ON TABLE user_info IS '用户信息表';
COMMENT ON COLUMN user_info.ID IS '用户ID';
COMMENT ON COLUMN user_info.username IS '用户姓名';
COMMENT ON COLUMN user_info.birthday IS '出生日期';
COMMENT ON COLUMN user_info.age IS '年龄';
COMMENT ON COLUMN user_info.sex IS '性别:0-female,1-male,2-unkonwn';
COMMENT ON COLUMN user_info.create_by IS '创建人员账号ID';
COMMENT ON COLUMN user_info.create_time IS '创建时间';
COMMENT ON COLUMN user_info.update_by IS '更新人员账号ID';
COMMENT ON COLUMN user_info.update_time IS '更新时间';

-- 添加列,COLUMN关键字这里可以省略
alter table user_info add COLUMN status int NOT NULL DEFAULT '1';

-- 修改列类型
alter table user_info alter column "status" TYPE SMALLINT;

-- 去掉列NOT NULL的设置
alter table user_info alter column "status" DROP NOT NULL;

-- 去掉列默认值设置
alter table user_info alter column "status" DROP DEFAULT;

-- 设置列 NOT NULL
alter table user_info alter column "status" SET NOT NULL;

-- 删除列
alter table user_info drop COLUMN "status";

-- 删除索引
drop INDEX IF EXISTS "unq_user_info";

-- 删除CONSTRAINT
alter table eng_cii_service_line_ship_leg_copy1 drop constraint if EXISTS 
"const_user_info_somekey";

-- 生成UUID, 需要uuid-ossp扩展
create extension "uuid-ossp";  -- 开启扩展(好像windows下, 14默认有安装)
select uuid_generate_v1();  -- 该UUID会泄露计算机标识和生成时间,不适合安全性要求较高的应用
select uuid_generate_v4();  -- 此函数会生成一个v4版本的UUID。算法完全依靠随机数
SELECT REPLACE(uuid_generate_v4()::VARCHAR, '-', ''); -- 去掉横杆
DROP EXTENSION "uuid-ossp";   -- 关闭扩展

-- 查看PostgreSQL数据库版本
select version()
-- PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit

关于序列

  1. 如果”id”字段定义为: id SERIAL NOT NULL,则查看表定义语句中id字段定义,会是:”id” int4 NOT NULL DEFAULT nextval(‘”demo”.user_info_id_seq’::regclass), 其中demo是当前schema,user_info_id_seq是自增sequence名称
  2. 可以使用 select nextval(‘user_info_id_seq’) 来初始化并生成第一个序列值或者获取下个序列值,括号中是序列名称;select currval(‘user_info_id_seq’)来查询user_info_id_seq序列当前值;select setval(‘user_info_id_seq’, 99);设置当前值为99;select lastval(); 获取当前会话中最近生成的序列值(当前会话必须有序列,并且序列有生成过序列值)

时间相关

  1. 时间减去指定小时数,或者计算时间差,使用create_time字段来做示例:
    • select create_time – INTERVAL ‘8 hour’ from user_info — 使用常数指定要减去的小时数
    • select create_time – concat(age ,’1 hour’)::INTERVAL from user_info — 使用age字段的值作为要减去的小时数
      • 还可以这样写: select create_time – age * INTERVAL ‘1 hour’ from user_info
    • select create_time – (update_time – INTERVAL ‘8 hour’) from user_info — 计算两个时间之间相差多少,结果为正/负以下格式的值:06:39:45
    • select ROUND(extract(epoch from create_time – (update_time – INTERVAL ‘8 hour’))/3600, 4) from user_info — 将时间差转换为秒数,再转换成小时并保留4位小数
  2. 时间戳字段如果 TIMESTAMP (6),则自动生成的值会是这样:2023-02-08 23:42:33.499779,TIMESTAMP字段默认=TIMESTAMP(6)