PostgreSQL Syntax error I do not understand

Question

I have the next SQL cmd that postgress says it is not correct:

SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "NOT"
  Position: 41
Location   : db/migration/postgresql/V2.5.0.20180730192730__schema-add-kerberos-to-source.sql (/opt/tomcat/webapps/WebAPI/WEB-INF/classes/db/migration/postgresql/V2.5.0.20180730192730__schema-add-kerberos-to-source.sql)
Line       : 1
Statement  : ALTER TABLE webapi.source ADD COLUMN IF NOT EXISTS krb_auth_method VARCHAR DEFAULT 'PASSWORD' NOT NULL

Any ideas why this is wrong? I am using psql 9.5.9

To be clear, the version of psql (the default command-line interface) does not have to be the same as the version of the Postgres server you are connecting to. SELECT version(); returns the version of the server you are currently connected to. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 6, 2019 at 21:37

Erwin Brandstetter · Accepted Answer · 2019-09-06 21:19:45Z

3

ALTER TABLE ... ADD COLUMN IF NOT EXISTS was added with Postgres 9.6. You can find it in the current manual.

You may want to upgrade to a current version anyway. Version 9.5 is getting old, version 12 will be out soon.

If you stick with 9.5, at least consider what the Postgres project advises:

We always recommend that all users run the latest available minor release for whatever major version is in use.

The current minor release is 9.5.19 at the time of writing. You are missing out on several years of security updates and bug fixes.

answered Sep 6, 2019 at 21:19

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Draco Ater · Accepted Answer · 2019-09-06 21:12:39Z

0

The manual says, there is no such thing as ADD COLUMN IF NOT EXISTS, only ADD COLUMN.

answered Sep 6, 2019 at 21:12

Draco Ater

21.3k8 gold badges66 silver badges88 bronze badges

1 Comment

Robert Horvick Over a year ago

Edit - I missed the version note. You are correct. ---- It exists in 9.6 - postgresql.org/docs/9.6/sql-altertable.html

Collectives™ on Stack Overflow

PostgreSQL Syntax error I do not understand

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related