Elad Benda Elad Benda - 2 months ago 18
SQL Question

postgres: syntax error at or near "BEGIN"

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"

Answer

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