Postgres syntax error in command consistent with documentation

Question

I am attempting to add a column to a table in postgres version 9.6.2.

$ psql --version
psql (PostgreSQL) 9.6.2

Accordingly, I am referencing the ALTER TABLE documentation for postgres 9.6.

The documentation says:

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] action [, ... ]

where action is one of:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

I have a table task:

=> select * from task;
 id | name
----+------
(0 rows)

on which I want to insert a column state using an idempotent command (i.e. it checks for whether the state column has been created already). That command is:

ALTER TABLE task ADD COLUMN IF NOT EXISTS state BIGINT NOT NULL;

However, this gives a syntax error at NOT:

=> ALTER TABLE task ADD COLUMN IF NOT EXISTS state BIGINT NOT NULL;
ERROR:  syntax error at or near "NOT"
LINE 1: ALTER TABLE task ADD COLUMN IF NOT EXISTS state BIGINT NOT N...

Is this command inconsistent with the documentation? How do I resolve the syntax error?

Note: The command works without error when I remove the IF NOT EXISTS phrase, but the resulting command in that case is not idempotent as desired.

Answer 1

You might write a stored function in Postgres < 9.6 for adding columns in a failure safe way. Here is a very simple version:

CREATE OR REPLACE FUNCTION add_column(in_statement TEXT, in_table TEXT, in_column TEXT, in_schema TEXT DEFAULT 'public') RETURNS BOOLEAN AS $_$
BEGIN
    PERFORM * FROM information_schema.columns WHERE table_name = in_table AND column_name = in_column AND table_schema = in_schema;
    IF FOUND THEN
        RETURN FALSE;
    ELSE
        EXECUTE in_statement;
        RETURN TRUE;
    END IF;
END
$_$ LANGUAGE plpgsql VOLATILE;

You can add columns by;

SELECT add_column('ALTER TABLE task ADD COLUMN state BIGINT NOT NULL', 'task', 'state');

The schema name is optional and only needs to be specified if the schema is not public.