Today I Learned

hashrocket A Hashrocket project

Where in with multiple values in postgres

Postgres has a record type that you can use with a comma seperated list of values inside of parenthesis like this:

> SELECT pg_typeof((1, 2));

 pg_typeof
-----------
 record
(1 row)

What is also interesting is that you can compare records:

> select (1, 2) = (1, 2);

 ?column?
----------
 t
(1 row)

And additionally, a select statement results in a record:

> select (1, 2) = (select 1, 2);

 ?column?
----------
 t
(1 row)

What this allows you to do is to create a where statement where the expression can check to see that 2 or more values are contained in the results of a subquery:

> select true where (1, 2) in (
  select x, y
  from
    generate_series(1, 2) x,
    generate_series(1, 2) y
);

 bool
------
 t
(1 row)

This is useful when you declare composite keys for your tables.

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.