How PostgreSQL WHERE clause work in this query?

Question

Can someone explain how this WHERE clause work in this query?

    SELECT * FROM "User" AS U
    WHERE ((U."ID", U."age") > (23820, 25));

As I understand Postgres returns users where the id is greater than 23820 and ignore the age column.

I recommend to just try out. I created an example here and created a second query which determines the identic output. dbfiddle.uk/… — Jonas Metzler
– Jonas Metzler, Commented Apr 22, 2022 at 14:11
@Bagrat thanks for asking such nice question. And thanks Jonas for the answer. — Kazi Mohammad Ali Nur Romel
– Kazi Mohammad Ali Nur Romel, Commented Apr 22, 2022 at 14:40
That's "tuple inequality". PostgreSQL is one of the few databases that implement it. — The Impaler
– The Impaler, Commented Apr 22, 2022 at 16:29
Thank you, @The Impaler. I prevented writing an answer instead of just a comment because I was just trying it out without being able to write a general summary. Maybe you could add it if you want :) — Jonas Metzler
– Jonas Metzler, Commented Apr 22, 2022 at 17:06

Rodrigo Cava · Accepted Answer · 2022-04-22 16:42:22Z

2

@JonasMetzler is correct on the comment. Postgres apparently uses this set as something on the lines of:

Most left parameter uses the operator, all the others use equal
OR
 repeat until one parameter left (
     Remove most left parameter + 
     Most left parameter uses the operator, all the others use equal 
  )

(parameter_n, [parameter_n-1, ..., parameter_n-final]) <operator> (value_n, [value_n-1, ..., value_n-final])

equals to

(parameter_n = value_n AND parameter_n-1 = value_n-1 AND ... parameter_n-final <operator> value_n-final)
OR 
(parameter_n = value_n AND parameter_n-1 = value_n-1 AND ... parameter_n-final-1 <operator> value_n-final-1)
OR 
...
(parameter_n = value_n AND parameter_n-1 <operator> value_n-1)
OR 
(parameter_n <operator> value_n)

answered Apr 22, 2022 at 16:42

Rodrigo Cava

1889 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Impaler Over a year ago

"...all the others use equal..." .. No. Tuples inequality works on all components of the tuple. If one component comparison results in equality it moves into the next one.

Collectives™ on Stack Overflow

How PostgreSQL WHERE clause work in this query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related