clear markdown feedback
clear markdown feedback
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;
name id active
Test1 3 t
Test2 4 t
Test3 5 t
 hidden batch(es)


SELECT * FROM service ORDER BY name;
name id active
Test1 3 f
Test2 4 t
Test3 5 t
Test4 6 t
 hidden batch(es)