PostgreSQL "if not exists" always throws syntax error

user_6506334 0
Viewed 768

I'm trying to run this:

IF NOT EXISTS (
   SELECT FROM information_schema.tables
   WHERE table_schema = 'topgun' AND table_name = 'session'
) 

THEN

CREATE TABLE topgun.session (
  sid varchar NOT NULL COLLATE "default",
  sess json NOT NULL,
  expire timestamp(6) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE topgun.session ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX "IDX_session_expire" ON topgun.session ("expire");     

END IF;

What I'm trying to achieve here is creating a table, a constraint and an index if the table doesn't exist.

I'm getting various syntax errors even when I change empty rows. IF NOT EXISTS always causes an error, no matter what I do or what comes after it. As if PostgreSQL didn't even know it. Even if I copy snippets from tutorials, PostgreSQL freaks out.

I'm using Heroku Postgres, version 12.4.

The SELECT part in itself works. The CREATE TABLE, ALTER TABLE and CREATE INDEX rows also work outside of the structure.

The error message is:

SQL Error [42601]: ERROR: syntax error at or near "IF"
  Position: 5
postgresql
3 Answers

Try with DO statement

do $$
begin
IF NOT EXISTS (
   SELECT FROM information_schema.tables
   WHERE table_schema = 'public' AND table_name = 'session'
) 

THEN

CREATE TABLE public.session (
  sid varchar NOT NULL COLLATE "default",
  sess json NOT NULL,
  expire timestamp(6) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE public.session ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX "IDX_session_expire" ON public.session ("expire");     

END IF;
end;
$$

DEMO

Better you use this approach:

create table if not exists test(Id int)
user_77724830

No need for PL/pgSQL, just use the IF NOT EXISTS option in the CREATE TABLE part:

CREATE TABLE IF NOT EXISTS topgun.session (
  sid varchar NOT NULL COLLATE "default",
  sess json NOT NULL,
  expire timestamp(6) NOT NULL,
  CONSTRAINT "session_pkey" PRIMARY KEY ("sid")
);

CREATE INDEX IF NOT EXISTS "IDX_session_expire" ON topgun.session ("expire");
user_3303150

I had to declare "LANGUAGE plpgsql" to avoid the error.

CREATE PROCEDURE init() AS $_$
BEGIN

IF NOT EXISTS (
   SELECT FROM information_schema.tables
   WHERE table_schema = 'topgun' AND table_name = 'session'
) 

THEN

CREATE TABLE topgun.session (
  sid varchar NOT NULL COLLATE "default",
  sess json NOT NULL,
  expire timestamp(6) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE topgun.session ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX "IDX_session_expire" ON topgun.session ("expire");     

END IF;
END; $_$ LANGUAGE plpgsql;

CALL init();
user_30706130
Related