CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sql
viraptor
viraptor

Reputation: 34155

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

Upvotes: 3

Views: 4022

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12020

It is possible though it is little bit hacky, however if you must stick to pure SQL solution, it may come handy. The trick is in utilizing XML features. Your result set actually returns data from XML and that XML is built from real table or is created as fake. (The fake data source is simple subquery - in this usecase it is 0 as you requested, in my other Oracle usecase I needed empty result set.)

with x(d) as (
  select query_to_xml('select version from ' || (
           select case count(*) when 0 then '(select 0 as version)' else 'versions' end
           from information_schema.tables t
           where t.table_name = 'versions' -- try 'nonexistent' table_name instead
         ) || ' limit 1', false, false, '')::xml
)
select xmltable.*
from x
   , xmltable('/table/row'
       passing d
       columns
         version text path 'version'
     )

fiddle

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656854

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:

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:

Upvotes: 2

Jim Jones
Jim Jones

Reputation: 19623

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)

Upvotes: 0

Related Questions