PostgreSQL: Populate rows into a table only if the table is empty

Question

Writting a SQL script to create tables. Among those tables are meta data tables.

I would like to insert these meta data, in SQL only if possible, only if :

1- The table is empty
   In this case all the values must be inserted

2- The table is not empty. In this case: 
   1. if the age_group key already exists, the table is updated with the new values 
   2. if the key doesn't exists, a new row is inserted

Examples

CREATE TABLE agegroup
(
    id integer NOT NULL DEFAULT nextval('agegroup_id_seq'),
    age_group text UNIQUE NOT NULL,
    population integer
    CONSTRAINT pk_agegroup PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_age_group ON agegroup(age_group);

INSERT INTO agegroup(age_group,population)
VALUES ('0- 4',25435), ('5 - 9',25333)

id	age_group	population
1	0-4	25435
2	5-9	25333

Then assuming the following INSERT (new age_group '10-14'):

INSERT INTO agegroup(age_group,population)
VALUES ('0-4',25435), ('5-9',25333), ('10-14',25311);

The table must look as follow:

id	age_group	population
1	0-4	25435
2	5-9	25333
3	10-14	25311

While with the following INSERT (the age_group population changed from 25435 to 25335):

INSERT INTO agegroup(age_group,population)
VALUES ('0-4',25335), ('5-9',25333), ('10-14',25311);

The table must look as follow:

id	age_group	population
1	0-4	25335
2	5-9	25333
3	10-14	25311

Questions

Saw a begining of solution around using ON DUPLICATE KEY UPDATE but seems it is only applicable for one value at time. Is there a simple way to achieve this with multiple values ?
In the case where the solution is too complicate, is there a way to INSERT into a table only if the table contains no data ?

So doing something like (dummy SQL):

INSERT IF TABLE EMPTY INTO agegroup(age_group,population)
VALUES ('0- 4',25435), ('5 - 9',25333)

Bergi · Accepted Answer · 2022-06-06 18:26:58Z

1

You're on the right track with ON DUPLICATE KEY, though in Postgres it's called ON CONFLICT. And it does work on individual rows:

CREATE TABLE IF NOT EXISTS agegroup (
--           ^^^^^^^^^^^^^
    id serial PRIMARY KEY,
--     ^^^^^^ ^^^^^^^^^^^ simple notation
    age_group text UNIQUE NOT NULL,
    population integer
);

-- CREATE UNIQUE INDEX idx_age_group ON agegroup(age_group);
-- Don't create a second unique index, the `UNIQUE` keyword already does that

INSERT INTO agegroup(age_group,population)
VALUES ('0-4',25335), ('5-9',25333), ('10-14',25311)
ON CONFLICT (age_group) DO UPDATE
SET population = EXCLUDED.population;

answered Jun 6, 2022 at 18:26

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Populate rows into a table only if the table is empty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related