PostgreSQL How to do IF expression is null?

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

Why not simply use `create table if not exists`? – Mar 10 '20 at 19:37 — , Mar 10 '20 at 19:37
@a_horse_with_no_name what I ended up doing :) – kafka Mar 11 '20 at 14:21 — kafka, Mar 11 '20 at 14:21

ChechoCZ · Answer 1 · 2020-03-10T17:20:58.253

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

edited Mar 10 '20 at 17:20

answered Mar 10 '20 at 16:57

ChechoCZ

I am looking for a table not a database... @ChechoCZ – kafka Mar 10 '20 at 17:01
Is your table in camel Case? Like 'myTable'? In that case, you should wrap the table name in double quotes. Have you tried it? – ChechoCZ Mar 10 '20 at 17:07
it is lower case... i just want to see if it there and if not create it – kafka Mar 10 '20 at 17:12
Kafka, I'll edit the answer with the solution. Hope it helps... – ChechoCZ Mar 10 '20 at 17:20
I need to trigger something if does not exist? how do i do that then? @ChechoCZ – kafka Mar 10 '20 at 18:53
You can do exactly what you were doing but instead of `SELECT to_regclass('public.entityName') IS NULL` you put the code I posted in the answer. You check if it exists, and proceed as you need to. – ChechoCZ Mar 10 '20 at 19:03

score 0 · Accepted Answer · answered Mar 19 '20 at 09:02

0

I ended doing thins

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

answered Mar 19 '20 at 09:02

kafka

2 Answers2