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