PostgreSQL – Common autoincrement with inherited tables

April 30, 2022

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

>Solution :

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