PostgreSQL "if not exists" always throws syntax error

Question

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

Does it throw the error if the body (then..body..end if) is removed? Is so, include the error message in the question. If not, reconsider the title. And, after consideration and coming up with an SSCCE, and simplifying the SQL, include the error message. — user2864740
– user2864740, Commented Sep 4, 2020 at 2:44

Akhilesh Mishra · Accepted Answer · 2020-09-04 03:28:07Z

3

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)

edited Sep 4, 2020 at 3:28

answered Sep 4, 2020 at 3:19

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

Tamás Polgár Over a year ago

This seems to work, but only if I put a line break between "do" and "$$" and a semicolon after the closing $$. Thanks! Anyway, what's the difference?

user330315 · Accepted Answer · 2020-09-04 04:49:22Z

1

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");

answered Sep 4, 2020 at 4:49

user330315

Comments

loop · Accepted Answer · 2021-12-23 19:48:30Z

0

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();

answered Dec 23, 2021 at 19:48

loop

1,0238 silver badges17 bronze badges

Collectives™ on Stack Overflow

PostgreSQL "if not exists" always throws syntax error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related