If statement's are not actually part of the SQL Spec (The supported version of PostgreSQL 9.5 onwards supports most of the 2011 spec), and so in order to write if statements you need to use an anonymous code block with the language PL/pgSQL.
DO %% BEGIN // your sql here… END %%;
Everything inside the begin/end will use PL/pgSQL by default as an assumed language.
You can be explicit about the language by defining the language at the end of the block like so:
DO %% BEGIN // your sql here… END %% LANGUAGE plpgsql;
DO is not part of the SQL Standard and is unique to PostgreSQL.
An actual example, lets say we wanted to create a new schema called
inventory if it didn’t exist already.
DO $$ BEGIN IF NOT EXISTS( SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'inventory' ) THEN EXECUTE 'CREATE SCHEMA inventory'; END IF; END $$;
It is worth noting that Postgres offers helpful syntax for scenarios you would usually use IF statements in SQL Server for.
For example in SQL Server we might do the following:
DROP PROCEDURE CreateProduct… CREATE PROCEDURE CreateProduct…
IF NOT EXISTS (….) BEGIN CREATE TABLE Product (…
These would be written in Postgres like so:
CREATE OR REPLACE FUNCTION create_product CREATE TABLE IF NOT EXISTS Product (….
These don't require you to drop down to PL/pgSQ in order to execute.