PostgreSQL How to do IF expression is null? [duplicate]

Question

I am trying to make a conditional Select statement that creates a table if the table does not exist, but this does not seem to work for some reason?

IF SELECT to_regclass('public.entityName') IS NULL
BEGIN
    CREATE TABLE |entityName| 
        (....)
END
END IF

ChechoCZ · Accepted Answer · 2020-03-10 17:20:58Z

0

You could use this to return true/false:

SELECT EXISTS 
(
  SELECT 1 
  FROM pg_tables
  WHERE schemaname = 'schema'
  AND tablename = 'table'
);

Hope it helps

answered Mar 10, 2020 at 16:57

ChechoCZ

3124 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

I am looking for a table not a database... @ChechoCZ

Is your table in camel Case? Like 'myTable'? In that case, you should wrap the table name in double quotes. Have you tried it?

it is lower case... i just want to see if it there and if not create it

Kafka, I'll edit the answer with the solution. Hope it helps...

I need to trigger something if does not exist? how do i do that then? @ChechoCZ

|

kafka · Accepted Answer · 2020-03-19 09:02:10Z

0

I ended doing thins

CREATE TABLE IF NOT EXISTS public.name
    (...);

answered Mar 19, 2020 at 9:02

kafka

7332 gold badges14 silver badges35 bronze badges