PostgreSQL - Common autoincrement with inherited tables

Question

I'm currently trying the inheritance system with PostgreSQL but I have a problem with the auto-increment index in my child tables.

I have three tables: "Currency", "Crypto" and "Stable"

CREATE TABLE IF NOT EXISTS public.currency
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(30) UNIQUE NOT NULL,
    symbol VARCHAR(10) UNIQUE NOT NULL,
);

CREATE TABLE IF NOT EXISTS public.stable (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);    
CREATE TABLE IF NOT EXISTS public.crypto (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);

I insered my data like this:

INSERT INTO public.stable (name, symbol) VALUES ('Euro', '€'), ('Dollar', '$'), ('Tether', 'USDT');    
INSERT INTO public.crypto (name, symbol) VALUES ('Bitcoin', 'BTC'), ('Ethereum', 'ETH'), ('Litcoin', 'LTC');

But this is my problem: I would like to have a unique identifier that increments itself through my parent table "Currency". When I select, I have (take a look in my id: 1,2,3,1,2,3):

But, Is it possible to have something like this instead (1,2,3,4,5,6):

Is it a problem in my primary key?

Thank you

D-Shih · Accepted Answer · 2022-04-30T02:34:12.203

1

We can try to use create sequence to set row numbers for sharing between multiple tables.

define a new sequence generator

create sequence n_id;

Then we can use this sequence as below, sharing this sequence for those three tables.

create sequence n_id;

CREATE TABLE IF NOT EXISTS currency
(
    id INT default nextval('n_id') PRIMARY KEY,
    name VARCHAR(30) UNIQUE NOT NULL,
    symbol VARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS stable (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);    
CREATE TABLE IF NOT EXISTS crypto (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);

sqlfiddle

edited Apr 30 '22 at 02:34

answered Apr 30 '22 at 02:28

D-Shih

44,943
6
31
51

So cool thank you! But is it the only way? I mean, I have an other parent table with 4 childs. Do I need to create a sequence too? Or this is the only one way to "share" the auto increment – Naografix Apr 30 '22 at 02:37
In my experience, create yourself `sequence` that shares the same increment id object for multiple tables – D-Shih Apr 30 '22 at 04:17

PostgreSQL - Common autoincrement with inherited tables

1 Answers1