Question 114152 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What happened when you use table1.* <> table2.*?

Imagine 2 table with the same structure but that might have some rows not equal for the same primary key.

What really happen when using a where clause like this : " where table1.* <> table2.* " ?

I "used" it in PostgreSQL but I’m interested for other’s languages behavior with this weird thing.

Tags:

2

Answers


  1. 0

    This statement is comparing, every column together of the first table to every column together of the second table. It is the same as writing the composite type explicitly, which would be required if the columns are not in the same order in both tables.

    (t1.id, t1.col1, t1.col2) <> (t2.id, t2.col2, t2.col2)

    or even more verbose

    t1.id <> t2.id
OR t1.col1 <> t2.col1
OR t1.col2 <> t2.col2

    But you may want to use IS DISTINCT FROM instead of <> to consider null vs not null as being different/not equal.

    Login or Signup to reply.
  2. 0

    In postgres t1.* <> t2.* in this context is expanded to be:

    (t1.c1, t1.c2, ..., t1.cn) <> (t2.c1, t2.c2, ..., t2.cn)

    which is the same as:

    (t1.c1 <> t2.c1) OR (t1.c2 <> t2.c2) OR ...

    I think the expansion is a postgres extension to the standard, tuple comparision exists in several other DBMS. You can read about it at https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

    The number of columns is required to be the same when comparing tuples, but
    I discovered something peculiar when trying your example:

    create table ta (a1 int);
create table tb (b1 int, y int);
select * from ta cross join tb where ta.* <> tb.*

    The last select succeds, despite the tuples having different number of columns. Adding some rows change that:

    insert into ta values (1),(2);
insert into tb values (2,1),(3,4);
select * from ta cross join tb where ta.* <> tb.* 
ERROR:  cannot compare record types with different numbers of columns

    so it appears as this is not checked when the statement is prepared. Expanding the tuple manually yields an ERROR even with empty tables:

    select * from ta cross join tb where (ta.a1, ta.a1) <> (tb.b1, y, y); 
ERROR:  unequal number of entries in row expressions

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search