How to SELECT without error if table does not exist

I'd like to run a SELECT from a given table, which should not fail if the table doesn't exist. Let's say, for simplicity, that the table contains only one column and one row and I'm after that scalar. If the table doesn't exist I want to get back 0.

I know I could do something like:

query("select count(1) from information_schema.tables where table_name = 'versions'")
if result > 0
  return query("select version from versions limit 1")
else
  return 0

Or I could do the equivalent using a stored procedure / plpgsql function.

But is there a way to do the same in a single ad-hoc query?

(I'm doing two queries. I don't have a good reason to change it. I'm just curious whether/how it's possible to do.)

Solution

You want a single round trip to the server with a pure SQL solution. There were many related requests over the course of the years. It's not possible on principle.

You either need dynamic SQL - which requires a function using PL/pgSQL or another PL.
Or you need two round trips to the server. First check existence, then query.

You also cannot nest this in a plain SQL function, which would plan every statement in the body before execution and fail while trying to resolve a non-existing table name. It would not even pass the superficial tests at function creation time. See:

Two simple solutions for your simple case (among many possible ways):

CREATE OR REPLACE FUNCTION f_select_version_if_exists1() 
  RETURNS int LANGUAGE plpgsql PARALLEL SAFE AS 
$func$
BEGIN 
   IF EXISTS (
      SELECT FROM pg_catalog.pg_tables
      WHERE  tablename  = 'versions'
      AND    schemaname = 'public'  -- see below!
      ) THEN
      RETURN (SELECT version FROM versions LIMIT 1);
   ELSE
      RETURN 0;
   END IF;
END 
$func$;

Or:

CREATE OR REPLACE FUNCTION f_select_version_if_exists2(INOUT _version int = 0) AS 
$func$
BEGIN 
   IF EXISTS (
      SELECT FROM pg_catalog.pg_tables
      WHERE  tablename  = 'versions'
      AND    schemaname = 'public'  -- see below!
      ) THEN
      SELECT INTO _version version
      FROM   versions LIMIT 1;
   END IF;
END 
$func$  LANGUAGE plpgsql PARALLEL SAFE;

I highly recommend to also pin down the schema name. Table names are not unique in Postgres. See:

How to check if a table exists in a given schema

Also, I made the function PARALLEL SAFE (which only matters if it might be nested in big queries). This would be wrong while relying on the search_path because that typically includes the schema for temporary objects, which makes it PARALLEL UNSAFE

The second is making creative use of an INOUT parameter. Related:

Can I make a plpgsql function return an integer without using a variable?