PostgreSQL – Better Way to List All Databases Except ‘postgres’

postgresqlselectwhere

SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')

Using the code above, I get all the databases except postgres as shown in the image below:

Is there any better/cleaner/nicer way than my code above to get the same result?

Best Answer

There is no real improvement possible, but I think the following is more readable:

SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';

`WITH ORDINALITY` in Postgres 9.4+

Introduced with Postgres 9.4. The manual:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

SELECT u.*
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN   users u USING (id)
ORDER  BY x.order_nr;

array or set?

x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:

SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.

Postgres 9.3 or earlier

For now, to preserve the order of elements, you could:

SELECT u.*
FROM  (
   SELECT arr, generate_subscripts(arr, 1) AS order_nr
   FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
   ) x
JOIN   users u ON u.id = x.arr[x.order_nr]
ORDER  BY x.order_nr;

db<>fiddle here

Best Answer

Related Solutions

Postgresql – Get a list of databases that have a certain table

PostgreSQL Order By – Order of Returned Rows with IN Statement

WITH ORDINALITY in Postgres 9.4+

array or set?

Postgres 9.3 or earlier

Related Question

`WITH ORDINALITY` in Postgres 9.4+