基础命令

\l 列出所有数据库
\c <db> 选择数据库
\dt 查看表列表
\d <table_name> 查看表结构
\q 退出
\set 显示所有变量设置
\pset 显示所有打印设置

-- 关键系统表别名
\set activity 'SELECT * FROM pg_stat_activity'
\set locks 'SELECT * FROM pg_locks'
\set config 'SELECT * FROM pg_settings'

:activity;
:locks;
:config;

\set AUTOCOMMIT off
\set ON_ERROR_STOP on

新建库和用户

create user [username] with password '[password]';
create database [database] owner [username];

远程登陆

psql -h localhost -p 5432 -U postgres -d postgres

序列操作

create sequence td.ba_if_sale_oper_low_sub_seq
minvalue 1000
maxvalue 99999999999
start with 311500
increment by 1
cache 4000
cycle
order;

SELECT * FROM information_schema.sequences where sequence_name like 'ba_if_sale%';

数据排重

WITH keep_rows AS (
  SELECT ctid, -- 使用物理行标识
    ROW_NUMBER() OVER (
      PARTITION BY group_field
      ORDER BY sort_field DESC -- 按排序字段保留第一条
    ) AS rn
  FROM your_table
)
DELETE FROM your_table
WHERE ctid IN (
  SELECT ctid FROM keep_rows WHERE rn > 1
);

时间戳转日期

select to_char(to_timestamp(1654617600),'YYYY-MM-DD HH24:MI:SS');