【Postgresql】创建时间、更新时间数据库端自动实现更新,postgresql修改表id字段为自增,创建序列，查询全部序列，删除序列，生成序列创建sql语句

postgresql数据表中
字段 解释
id： 自增数字；
name：字符串；
create_at：记录创建数据的时间；
update_at：记录更新记录的时间；

想法：
create_at 和 update_at 字段用于记录记录的创建和更新时间，可以通过数据库的默认值和触发器来实现自动处理这些时间戳字段，而不需要在 Python 后端每次请求时手动传递这些参数。

1.创建 create_at 字段的默认值：
在创建表时，可以为 create_at 字段设置默认值为当前时间，这样在插入新记录时，create_at 字段会自动记录创建时间。

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP
);

2.使用触发器自动更新 update_at 字段：
可以创建一个触发器（Trigger），在更新记录时自动将 update_at 字段设置为当前时间。

首先，创建一个触发器函数：

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后，创建触发器来调用这个函数：

CREATE TRIGGER update_timestamp_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE PROCEDURE  update_timestamp();

效果：
当插入一条新记录时，create_at 字段会自动设置为当前时间，update_at 字段保持为空或可以初始化为 NULL。
当更新一条记录时，update_at 字段会自动设置为当前时间。

遇到问题：

1.在创建触发器调用函数时使用 EXECUTE PROCEDURE 出现报错：SQL 错误 [42601]: ERROR: syntax error at or near “FUNCTION” Position: 90

原因：
PostgreSQL 11 及更早版本需要使用 EXECUTE PROCEDURE。
查看版本：
SELECT version();

PostgreSQL 10.23 (Ubuntu 10.23-0ubuntu0.18.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

解决方法：
使用 EXECUTE PROCEDURE 代替 EXECUTE FUNCTION
在 PostgreSQL 11 及更早的版本中，触发器语法使用 EXECUTE PROCEDURE 而不是 EXECUTE FUNCTION。更新你的触发器创建语句如下：
CREATE TRIGGER update_timestamp_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE PROCEDURE update_timestamp();

from sqlalchemy import Column, Integer, String, DateTime, func
from db.base_class import Base


class Demo(Base):
    # 表的名字:
    __tablename__ = 'demo'

    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, index=True)
    name = Column(String(32))
    create_at = Column(DateTime, default=func.now())
    update_at = Column(DateTime, onupdate=func.now())
	

 

 

• 先创建关联序列，序列名称是depot_item_id_seq ，起始数为1。

CREATE SEQUENCE depot_item_id_seq START 1;

• 在字段默认值里设 nextval('depot_item_id_seq '::regclass) 即可

nextval('depot_item_id_seq '::regclass)

PostgreSQL创建序列，查询全部序列，删除序列，生成序列创建sql语句

序列号（SERIAL）类型

smallserial(int2),
serial(int4)
bigserial(int8)

CREATE TABLE t_testsmaserial (
    "Id" serial,
    "Name" VARCHAR (200)
);

序列函数

函数	返回类型	描述
currval(regclass)	bigint	返回最近一次用 nextval 获取的指定序列的数值
nextval(regclass)	bigint	递增序列并返回新值
setval(regclass, bigint)	bigint	设置序列的当前数值
setval(regclass, bigint, boolean)	bigint	设置序列的当前数值以及 is_called 标志

-- 设置序列的当前数值：
 SELECT
    setval(
        '"aud_alter_id_seq"' :: regclass,
        100
    );

-- 获取当前序列值：
SELECT
    currval(
        '"aud_alter_id_seq"' :: regclass
    );

-- 递增序列并返回新值：
SELECT
    nextval(
        '"aud_alter_id_seq"' :: regclass
    );

生成序列创建sql语句

--查询全部序列
SELECT
	* 
FROM
	pg_class 
WHERE
 relkind = 'S'
 -- and relowner = (SELECT usesysid FROM pg_user WHERE usename = '用户名') 


--生成已有序列创建语句
select 
'CREATE SEQUENCE '|| relname ||' INCREMENT BY 1;' 
FROM
	pg_class 
WHERE
 relkind = 'S'

创建序列

CREATE SEQUENCE act_evt_log_log_nr__seq INCREMENT BY 1 ;
CREATE SEQUENCE aud_alter_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE client_debug_log_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE global_id_sequence INCREMENT BY 1 ;
CREATE SEQUENCE kx_cost_put_customer_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_cost_put_items_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE t_certificate_c_certificateid_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_kpi_month_history_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_productid INCREMENT BY 1 ;
CREATE SEQUENCE price_id INCREMENT BY 1 ;
CREATE SEQUENCE kx_pinventoryid INCREMENT BY 1 ;
CREATE SEQUENCE kx_portrait_detail_seq INCREMENT BY 1 ;
CREATE SEQUENCE price_id_detail INCREMENT BY 1 ;
CREATE SEQUENCE price_manu INCREMENT BY 1 ;
CREATE SEQUENCE price_mid_id INCREMENT BY 1 ;
CREATE SEQUENCE sequence_portrait_detail INCREMENT BY 1 ;
CREATE SEQUENCE tbl_xulie2_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE utl_seq_number_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE bw_maindata_seq INCREMENT BY 1 ;
CREATE SEQUENCE file_task_id_seq INCREMENT BY 1 ;

DROP SEQUENCE seq_test;

CREATE SEQUENCE seq_test INCREMENT BY 2 MINVALUE 1 MAXVALUE 5 START WITH 2 NO CYCLE;

INCREMENT BY ： 每次序列增加（或减少）的步长

MINVALUE ： 序列最小值，NO MINVALUE表示没有最小值

MAXVALUE ： 序列最大值，NO MAXVALUE表示没有最大值

START WITH ：以什么序列值开始

CYCLE ： 序列是否循环使用

OWNED BY ： 可以直接指定一个表的字段，也可以不指定。

 

1、创建自增序列

CREATE SEQUENCE table_name_id_seq START 1;

2、设置字段默认值

字段默认值中设置 nextval(‘table_name_id_seq’)

3、常用查询

-- 查询所有序列
select * from information_schema.sequences where sequence_schema = 'public';

-- 查询自增序列的当前值
select currval('table_name_id_seq');

-- 查询自增序列的下个值
select nextval('table_name_id_seq');

-- 正在使用的连接数
SELECT count(*) FROM pg_stat_activity;

-- 不包含当前查询进程的连接数
SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();

-- 查看某个表的所有索引信息
select * from pg_indexes where tablename='table_name';
select * from pg_statio_all_indexes where relname='table_name';