How to SELECT without error if table does not exist

Question

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.)

The planner will look up all relations in your query at planning time. You can "delay" the planning time by using a function, but in a simple query that's a problem. — A. Scherbaum, Jan 19 '20 at 04:58

score 2 · Accepted Answer · answered Jan 20 '20 at 02:37

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?

score 0 · Answer 2 · edited Jan 19 '20 at 11:16

I believe a short PLpg/SQL function is what you're looking for. You can check if the table exists in the information_schema.tables as you've been doing and store this result in a variable. After that you can evaluate the result set in an IF statement and return whatever you want, e.g.

CREATE OR REPLACE FUNCTION f(p_table_name TEXT) 
  RETURNS INT 
LANGUAGE plpgsql AS 
$BODY$
DECLARE 
  res INT;
BEGIN 
  SELECT count(*) 
     into res
  FROM information_schema.tables 
  WHERE table_name = p_table_name;

  IF res>0 THEN RETURN res;
  ELSE RETURN 0;
  END IF;
END 
$BODY$;

Non-existing table

SELECT * FROM f('versions');
 f 
---
 0
(1 Zeile)

Existing table

SELECT * FROM f('t');
 f 
---
 1
(1 Zeile)

Thanks. This is included in the question though, I'm specifically after a not-plsql solution. — viraptor, Jan 20 '20 at 00:57

How to SELECT without error if table does not exist

2 Answers2