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.

What does select version(); say? Just because psql is from 9.6.2 doesn't mean that the server you're connecting to is 9.6.2. — mu is too short
– mu is too short, Commented Aug 2, 2018 at 3:53
@muistooshort you are right. select version() showed the version to be 9.4, and the documentation for 9.4 does not show a IF NOT EXISTS option for created columns. It seems I'll have to think of how to make the command idempotent without that option. — mherzl
– mherzl, Commented Aug 2, 2018 at 4:38

clemens · Accepted Answer · 2018-08-02 06:21:56Z

3

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.

answered Aug 2, 2018 at 6:21

clemens

17.9k12 gold badges52 silver badges71 bronze badges

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

Collectives™ on Stack Overflow

Postgres syntax error in command consistent with documentation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related