By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798686 fiddles created (41871 in the last week).
select version();
version
PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
…
hidden batch(es)
CREATE TABLE service
(
name VARCHAR (10) NOT NULL PRIMARY KEY,
id INTEGER NOT NULL,
active BOOLEAN NOT NULL
);
✓
hidden batch(es)
INSERT INTO service VALUES
('Test1', 3, true), ('Test2', 4, true), ('Test3', 5, true);
3 rows affected
hidden batch(es)
SELECT * FROM service;
name
id
active
Test1
3
t
Test2
4
t
Test3
5
t
…
hidden batch(es)
CREATE TEMPORARY TABLE api
(
name VARCHAR (10) NOT NULL PRIMARY KEY,
id INTEGER NOT NULL
-- active BOOLEAN NOT NULL
);
✓
hidden batch(es)
INSERT INTO api VALUES
('Test2', 4), ('Test3', 5), ('Test4', 6);
3 rows affected
hidden batch(es)
SELECT * FROM api;
name
id
Test2
4
Test3
5
Test4
6
…
hidden batch(es)
WITH cte1 (nom1) AS
(
INSERT INTO service (name, id, active)
SELECT a.name, a.id, true FROM api a
WHERE a.name NOT IN (SELECT name FROM service) RETURNING name
),
cte2 (nom2) AS
(
UPDATE service s SET active = false
WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name
)
SELECT * FROM service;
