PostgreSQL create table statement: auto insert id

Question

我正在使用PostgreSQL 14。对于租户系统，My script

DROP TABLE IF EXISTS tenant;
CREATE TABLE tenant
(
id               smallserial primary key,
company_tax_code character varying(14),
period           character varying(16), -- 2021070420220705
created          timestamp with time zone
);
DROP TABLE IF EXISTS account_default;
CREATE TABLE account_default
(
id               smallint,
ref_type         smallint               not null,
ref_type_name    character varying(256),
voucher_type     smallint               not null,
column_name      character varying(64)  not null,
column_caption   character varying(128) not null,
filter_condition character varying(1024),
default_value    character varying(32),
sort_order       smallint,
created          timestamp with time zone,
created_by       character varying(64),
modified         timestamp with time zone,
modified_by      character varying(64),
tenant_id        smallint,
PRIMARY KEY (id, tenant_id),
CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);

我看起来像

DROP TABLE IF EXISTS account_default;
CREATE TABLE account_default
(
id               smallint default value (max_value of account_default.id + 1 where tenant_id = account_default.tenant_id,
ref_type         smallint               not null,
ref_type_name    character varying(256),
voucher_type     smallint               not null,
column_name      character varying(64)  not null,
column_caption   character varying(128) not null,
filter_condition character varying(1024),
default_value    character varying(32),
sort_order       smallint,
created          timestamp with time zone,
created_by       character varying(64),
modified         timestamp with time zone,
modified_by      character varying(64),
tenant_id        smallint,
PRIMARY KEY (id, tenant_id),
CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);

帮我

DROP TABLE IF EXISTS account_default;
CREATE TABLE account_default
(
id               smallint default value (max_value of account_default.id + 1 where tenant_id = account_default.tenant_id

我需要组合键

(table_id, tenant_id)
(1, 1)
(2, 1)
(3, 1)
(1, 2)
(2, 2)
(1, 3)

如果插入，我需要

(4, 1)

解释

4 = (max (table_id) where tenant_id = 1) + 1;

在数据库端插入触发器或自动化。

Answer 1

demo

• 将所有字符改为文本数据类型。
将小整型数据类型更改为大整型。
• 删除了一些非null约束。
• 开销是计算当前表account_default中的新租户id。

---

函数和触发器。

CREATE OR REPLACE FUNCTION restart_seq ()
RETURNS TRIGGER
AS $$
BEGIN
RAISE NOTICE 'new.tenant_id: %', NEW.tenant_id;
RAISE NOTICE 'all.tenant_id: %', (
SELECT
array_agg(tenant_id)
FROM
tenant);
RAISE NOTICE 'new.tenant_id in : %', (
SELECT
NEW.tenant_id IN (
SELECT
tenant_id
FROM
tenant));
IF (NEW.tenant_id NOT IN (
SELECT
tenant_id
FROM
account_default)) THEN
NEW.acc_id = 1;
ALTER SEQUENCE account_default_acc_id_seq
RESTART WITH 2;
RAISE NOTICE 'currval(''account_default_acc_id_seq''): %', currval('account_default_acc_id_seq');
END IF;
RETURN new;
END;
$$
LANGUAGE plpgsql;
create or replace trigger  trg_reset_the_accid
before insert on account_default
for each  row execute  procedure restart_seq();