postgres: syntax error at or near "BEGIN"

Question

I read this post Check if table exists in SQL Server

and wrote this script:

do $$
begin

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'st'
                 AND  TABLE_NAME = 'config_change_log'))
BEGIN
   CREATE SCHEMA AUTHORIZATION st
   CREATE TABLE st.config_change_log
(
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  last_config_version varchar(255),
  is_done Boolean,
  chage_description varchar(255)
)

END

end
$$

however when I run:

psql -h localhost -d myDb -U myUser -f myScript.sql

I get this error:

psql:myScript.sql:21: ERROR:  syntax error at or near "BEGIN"

score 1 · Answer 1 · answered Sep 13 '16 at 12:39

1

IF blocks look like this:

IF <condition>
THEN
   <statements>
[ELSIF <condition>
THEN
   <statements>]
[ELSE
   <statements>]
END IF;

answered Sep 13 '16 at 12:39

Laurenz Albe

209,280
17
206
263

score 1 · Answer 2 · answered Sep 13 '16 at 12:41

Is it this you are looking for?

DO $$
BEGIN

IF (NOT EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'st'
                 AND  TABLE_NAME = 'config_change_log'))
THEN
    CREATE SCHEMA st;
    CREATE TABLE st.config_change_log
    (
        id serial NOT NULL PRIMARY KEY,
        last_config_version varchar(255),
        is_done Boolean,
        chage_description varchar(255)
    );
END IF;

END
$$

score 0 · Accepted Answer · answered Sep 13 '16 at 13:03

This is much easier in Postgres, no need for an IF:

CREATE SCHEMA IF NOT EXISTS st;
CREATE TABLE IF NOT EXISTS st.config_change_log
(
  id serial NOT NULL PRIMARY KEY,
  last_config_version varchar(255),
  is_done Boolean,
  chage_description varchar(255)
);

postgres: syntax error at or near "BEGIN"

3 Answers3