Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create schema schema1;
create table schema1.target_table(
  id int,
  data1 text, 
  data2 text,
  constraint pix1 primary key (id))
  with(fillfactor=50);
insert into schema1.target_table 
select n, 'data1_'||n, 'data2_'||n
from generate_series(0,11e4)_(n);

create schema schema2;
create table schema2.source_table(
  id int,
  data1 text, 
  data2 text,
  constraint pix2 primary key (id))
  with(fillfactor=50);
insert into schema2.source_table
select n, 'data1_'||n, 'data2_'||n
from generate_series(8e4,12e4)_(n);

CREATE SCHEMA



CREATE TABLE



ERROR:  could not extend file "base/5/16385": No space left on device
HINT:  Check free disk space.


reindex table schema1.target_table;

ERROR:  schema "schema1" does not exist


vacuum analyze schema1.target_table;

ERROR:  schema "schema1" does not exist


cluster schema1.target_table using pix1;

ERROR:  schema "schema1" does not exist


reindex table schema2.source_table;

ERROR:  schema "schema2" does not exist


vacuum analyze schema2.source_table;

ERROR:  schema "schema2" does not exist


cluster schema2.source_table using pix2;

ERROR:  schema "schema2" does not exist


--https://stackoverflow.com/q/78047815/5298879
MERGE INTO schema1.target_table AS t
USING schema2.source_table AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        data1 = s.data1,
        data2 = s.data2
WHEN NOT MATCHED BY TARGET
    INSERT (id, data1, data2,)
    VALUES (s.id, s.data1, s.data2)
WHEN NOT MATCHED BY SOURCE
    DELETE;

ERROR:  syntax error at or near "BY"
LINE 9: WHEN NOT MATCHED BY TARGET
                         ^


begin;
create table timing as select clock_timestamp() started;

explain analyze verbose
delete from schema1.target_table 
  where id not in (select id from schema2.source_table);

explain analyze verbose
insert into schema1.target_table
table schema2.source_table
on conflict(id) do update set data1=excluded.data1,
                              data2=excluded.data2;

select clock_timestamp()-started as duration from timing;
rollback;

BEGIN



SELECT 1



ERROR:  relation "schema1.target_table" does not exist
LINE 5: delete from schema1.target_table 
                    ^


vacuum analyze schema1.target_table; 

ERROR:  current transaction is aborted, commands ignored until end of transaction block


cluster schema1.target_table using pix1;

ERROR:  current transaction is aborted, commands ignored until end of transaction block


begin;
create table timing as select clock_timestamp() started;

truncate schema1.target_table;

explain analyze verbose
insert into schema1.target_table
table schema2.source_table
on conflict(id) do update set data1=excluded.data1,
                              data2=excluded.data2;

select clock_timestamp()-started as duration from timing;
rollback;

ERROR:  current transaction is aborted, commands ignored until end of transaction block


vacuum analyze schema1.target_table; 

ERROR:  current transaction is aborted, commands ignored until end of transaction block


cluster schema1.target_table using pix1;

ERROR:  current transaction is aborted, commands ignored until end of transaction block


begin;
--https://stackoverflow.com/a/78047934/5298879
explain analyze verbose
with cte as (
  delete from schema1.target_table 
  where id not in (select id from schema2.source_table) )
insert into schema1.target_table
table schema2.source_table
on conflict(id) do update set data1=excluded.data1,
                              data2=excluded.data2;
rollback;

ERROR:  current transaction is aborted, commands ignored until end of transaction block


vacuum analyze schema1.target_table; 

ERROR:  current transaction is aborted, commands ignored until end of transaction block


cluster schema1.target_table using pix1;

ERROR:  current transaction is aborted, commands ignored until end of transaction block


begin;
--https://stackoverflow.com/a/52317534/5298879
explain analyze verbose
WITH upsert(id) AS (
   INSERT INTO schema1.target_table (id,data1,data2)
   SELECT id,data1,data2 FROM schema2.source_table
   ON CONFLICT (id) DO UPDATE SET
     data1 = excluded.data1,
     data2 = excluded.data2
   RETURNING id )
-- Delete any records in target that aren't in source table
DELETE FROM schema1.target_table
WHERE id NOT IN (SELECT id FROM upsert);

rollback;

ERROR:  current transaction is aborted, commands ignored until end of transaction block