PostgreSQL:只在表为空时才填充行

Question

编写SQL脚本创建表。这些表中包括元数据表。

我想在SQL中插入这些元数据，只有在可能的情况下，只有在:

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

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)

2543525333

Answer 1

您在ON DUPLICATE KEY的正确轨道上，尽管在Postgres中它被称为ON CONFLICT。它在单独的行上工作:

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;