Search code examples
postgresqldatabase-sequence

PostgreSQL - Common autoincrement with inherited tables

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):

enter image description here

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

enter image description here

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