PostgreSQL: Select from table only if table exists

Question

I am an SQL noob, and I have been trying to select data only if a table exists, and nothing if it doesn't. I've looked at various solutions, and this is the best I've been able to come up with:

DO $$                  
    BEGIN 
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables 
              WHERE  table_schema = 'public'
              AND    table_name = "Test_Table"
            )
        THEN
            SELECT
              test_col
            FROM "Test_Table"
            ORDER BY time ASC
        END IF ;
    END
$$ ;

but this gives:

ERROR:  syntax error at or near "END"
LINE 14:         END IF ;

Note that the table name may contain uppercase characters, so I believe the double quotes are necessary.

Any ideas? Maybe trying to catch an exception would be a better approach?

Thanks!

It is totally unclear what you are trying to do. Are you aware that `select into` creates a *table* and not a value for a column? — Gordon Linoff, Dec 24 '20 at 02:13

score 0 · Answer 1 · answered Dec 24 '20 at 09:45

0

DO $$                  
    BEGIN 
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables 
              WHERE  table_schema = 'public'
              AND    table_name = 'Test_Table' -- use single quotes, it's value
            )
        THEN
            SELECT
              test_col
            FROM "Test_Table" -- advice: never use Upper case and you don't need double quotes
            ORDER BY time ASC; -- use a ; at the end of the query
        END IF ;
    END
$$ ;

answered Dec 24 '20 at 09:45

Frank Heikens

117,544
24
142
135

Thanks for the suggestions! The `;` does fix the syntax error, and the query works as desired if the table does not exist. However, if the table does exist, I get: `ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 10 at SQL statement` instead of seeing the results returned like I am used to. Any ideas on how to actually get those rows? – nabelekt Dec 28 '20 at 17:45

score -1 · Answer 2 · answered Dec 24 '20 at 00:26

There are 2 issues in your block, both involving the select statement:

The select statement does not have the required terminating semi-colon (;)
Since the select is in a DO block it requires the INTO clause for columns selected.

Try:

DO $$  
    declare 
       l_test_col "Test_Table".test_col%type = 'Nothing selected: table does not exist.'                
    BEGIN 
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables 
              WHERE  table_schema = 'public'
              AND    table_name = "Test_Table"
            )
        THEN
            SELECT
              test_col
             into l_test_col
            FROM "Test_Table"
            ORDER BY time ASC
        END IF ;
        
        rise notice 'Result: %',l_test_col;  
    END
$$ ;

The above is restricted to "Test_Table" containing exactly 1 row if it does exist.

This won't work when the table doesn't exist: l_test_col depends on the existence of this table. — Frank Heikens, Dec 24 '20 at 09:42
@FrankHeikens: OOPS. True, and so does the select itself. Guess that comes from *never* creating a table in a procedure. You just forget little things like that! — Belayer, Dec 24 '20 at 17:02

PostgreSQL: Select from table only if table exists

2 Answers2