'psql: how to exit with error if query returns 'False'?

I'm using psql to check if a table exists in a given database. The command below works fine to return t for True or f for False:

psql -U $user -d $db -t -c "SELECT EXISTS (
    SELECT FROM pg_tables WHERE tablename='$wanted');"

When the table doesn't exist, I get 'f'.

Instead, I would like psql to exit with a non-zero exit status if the query returned False.

Is that possible?

sqlpostgresqlpsql


Solution 1:[1]

Try this:

psql -U $user -d $db -t -c "select * from '$wanted' limit 1"
echo $?

If the table does not exist the exit code is 1.

Solution 2:[2]

In general, I try to avoid provoking errors from SQL queries. Rather, I aim to work with the results they return and make sense of them.

That said, you could (quite hacky approach!) provoke a division by zero:

SELECT (1 / (SELECT count(*) FROM pg_tables WHERE tablename='$wanted'))::int::boolean;

If a table with the desired name is not existing, the query will fail with ERROR: division by zero. If a table with the desired name is existing, the query will return true.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name
Solution 2 Maximilian C.

Related Questions