PostgreSQL 9.3: IF NOT EXISTS

Question

I want to check whether the table exists or not in the database.

IF NOT EXISTS (SELECT * from INFORMATION_SCHEMA.Tables WHERE Table_name = 'test') THEN

    RAISE INFO 'Not exists';

else

    RAISE INFO 'Exists';

end if;

Getting an error:

ERROR:  syntax error at or near "IF"

score 10 · Accepted Answer · answered Feb 25 '15 at 06:29

10

DO
$do$
BEGIN
IF NOT EXISTS (SELECT * from INFORMATION_SCHEMA.Tables WHERE Table_name = 'test') THEN

    RAISE INFO 'Not exists';

else

    RAISE INFO 'Exists';

end if;
end;
$do$

You should surround your postgresql statements with block

answered Feb 25 '15 at 06:29

DAlekperov

141
5

why this works? – thepurpleowl Aug 21 '19 at 08:06

score 0 · Answer 2 · answered Feb 25 '15 at 06:29

IF NOT EXISTS is not valid in that context within (what appears like) plpgsql.

It can be used in contexts like CREATE TABLE IF NOT EXISTS foo.

In a plpgsql context, you should use GET DIAGNOSTICS combined with ROW_COUNT.

The doc gives the following code snippet for that:

GET DIAGNOSTICS integer_var = ROW_COUNT;

If integer_var (which you will need to have previously declared) is 0, you know that the table doesn't exist.

See the Postgres doc for more details.

PostgreSQL 9.3: IF NOT EXISTS

2 Answers2