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)

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:

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:

Questions

1. 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 ?

2. 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)

CodePudding user response：

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;