Re: support for MERGE

Op 13-01-2022 om 13:43 schreef Alvaro Herrera:
> Apologies, there was a merge failure and I failed to notice. Here's the
> correct patch.
>
> (This is also in github.com/alvherre/postgres/tree/merge-15)

> [20220113/v6-0001-MERGE-SQL-Command-following-SQL-2016.patch]

Good morning,

I got into this crash (may be the same as Jaime's):

#-----
# bash

t1=table1
t2=table2

psql -qX << SQL
drop table if exists $t1 cascade;
drop table if exists $t2 cascade;
create table $t1 as select /*cast(id::text as jsonb) js,*/ id from
generate_series(1,20) as f(id);
create table $t2 as select /*cast(id::text as jsonb) js,*/ id from
generate_series(1,20) as f(id);
delete from $t1 where id % 2 = 1;
delete from $t2 where id % 2 = 0;
( select 't1 - target', count(*) t1 from $t1
union all select 't2 - source', count(*) t2 from $t2 ) order by 1;

merge into $t1 as t1 using $t2 as t2 on t1.id = t2.id
when not matched and (t2.id > 10) and (t1.id > 10)
then do nothing
when not matched then insert values ( id )
when matched then do nothing ;

( select 't1 - target', count(*) t1 from $t1
union all select 't2 - source', count(*) t2 from $t2 ) order by 1 ;

SQL
#-----

Referencing alias 't1' in the WHEN NOT MATCHED member seems what
triggers his crash: when I remove the phrase 'and (t1.id > 10)', the
statement finishes correctly.

And I don't know if it is related but if I use this phrase:

when not matched and (id > 10)

I get:

ERROR: column "id" does not exist
LINE 2: when not matched and id > 0 -- (t2.id > 10) and (t1.id > 10)
^
HINT: There is a column named "id" in table "t1", but it cannot be
referenced from this part of the query.

Is that hint correct? Seems a bit strange.

Thanks,

Erik Rijkers