Execute select statement IF table exists

Question

FINAL PURPOSE: Generate a query that, if a table exists, execute a statement for that table

I'm trying to execute a PSQL (9.6) statement only if a certain table exists in the schema, but it always responds with a syntax error each time I try to use conditional IF.

My query is something like...

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
  SELECT * FROM users;
END IF;

And the output is...

ERROR:  syntax error at or near "IF"
LINE 1: IF EXISTS(SELECT 1 FROM information_schema.tables WHERE tabl...
        ^

There is no more code than this. All alternatives that I tried failed.

Solution

You could (and likely have to) wrap that in a function.

CREATE FUNCTION select_if_exists
                ()
RETURNS TABLE (id integer,
               foo text)
AS
$$
BEGIN
  IF EXISTS(SELECT *
                   FROM information_schema.tables
                   WHERE table_schema = current_schema()
                         AND table_name = 'elbat') THEN
    RETURN QUERY SELECT elbat.id,
                        elbat.foo
                        FROM elbat;
  END IF;
END;
$$
LANGUAGE plpgsql;

If you call

SELECT *
       FROM select_if_exists();

when the table does not exist you get the empty set.

Create the table, call it again and you'll get the contents of the table.

CREATE TABLE elbat
             AS SELECT 1::integer id,
                       'Hello World!'::text foo;

SELECT *
       FROM select_if_exists();

But you cannot distinguish from outside, just by calling the function, if you got an empty set because the table doesn't exist or because it is empty. You'd have to RAISE an error then (But if you want that you could have just used a normal SELECT that croaks anyway, if the target table doesn't exist.)

And the table has to have the expected columns. Otherwise the SELECT in the function fails.

Notice: If you actually don't want to return a query result but execute a DML (or a DDL should work too) you could also put it in an anonymous block without defining a function. Example:

DO
$$
BEGIN
  IF EXISTS(SELECT *
                   FROM information_schema.tables
                   WHERE table_schema = current_schema()
                         AND table_name = 'elbat') THEN
    DELETE FROM elbat;
  END IF;
END;
$$
LANGUAGE plpgsql;

Comments

[Andrei Suvorkov]

Try SELECT IF EXISTS

[Gordon Linoff]

You would have to use dynamic SQL for this. Parsing the SELECT will fail if the table doesn't exist.

[sticky bit]

That statement has to be in a PL/pgSQL block. Either you don't have it in such a block or you have an error already before the IF. If you got it in a block, please show the whole block.

[MARC.RS]

@stickybit there is no other block involved. How it would be written with a PL/pgSQL block?

[MARC.RS]

Nope, another fail ERROR: relation "users" does not exist

[MARC.RS]

I downvote you because you miss the other half of the question

[MARC.RS]

There is no error, but you only are checking if a table exists. My problem is that I cannot find a solution to execute a query if this is true.

[MARC.RS]

With the updated query it fails to, with ERROR: syntax error at or near "if" LINE 1: if (select count(*) from information_schema.tables where tab... ` ^`

[Kaval Patel]

have you try to run this solution

[MARC.RS]

Yes, and it say that exists. Only this.

[Kaval Patel]

you have to just modify a query. I will do that for you.please check it.

[Kaval Patel]

Let us continue this discussion in chat.

[MARC.RS]

Good approach! My idea is to use it to create some SQL Views, so its quite difficult to use this but, by now, is the only solution that seems to work

[Jodiug]

This does not work because the query parser will reject FROM table_name if it does not exist, even before starting execution of the WHERE clause.