dropping constraint throws error

Question

I have written my own script to generate a restore SQL file and one of the first tasks to do the restore is delete all the constraints that should be there. (Don't worry, I am limiting my script to ones that exist at the time of the backup.)

Here is a generic code snippet

ALTER TABLE ONLY data DROP CONSTRAINT IF EXISTS data_pkey;

Testing it on my own development environment it works as expected. However, running it on the production server that same code throws an error:

ERROR: syntax error at or near "EXISTS" LINE 1: ALTER TABLE ONLY data DROP CONSTRAINT IF EXISTS data_pkey;

If the IF EXISTS part is removed and the syntax is:

ALTER TABLE ONLY data DROP CONSTRAINT data_pkey;

the script runs without a problem.

I checked postgres.org under the ALTER TABLE section and my syntax is valid.

Versions of both installations? The IF EXISTS was introduced in Postgres 9.2. — Milen A. Radev
– Milen A. Radev, Commented Feb 6, 2014 at 18:21
IF EXISTS has to have been introduced before 9.2 for my development environment runs 9.1.11. But I think you answered the question. Querying the version through phpPgAdmin on the production server and it is merely 8.4, not 9.1 as documentation led me to believe, — dwlamb
– dwlamb, Commented Feb 6, 2014 at 18:32

Craig Ringer · Accepted Answer · 2014-02-07 01:42:49Z

1

As a workaround for old versions you can either:

Unconditionally drop the constraint and trap the error; or
Check information_schema or the pg_catalog for the constraint, and only drop it if you find it already exists.

answered Feb 7, 2014 at 1:42

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

1 Comment

dwlamb Over a year ago

Nice anticipation of my follow-up question: Since I can not use IF EXISTS because of the version as 8.4, how do I deploy the script? :-) Thanks for the answer

Collectives™ on Stack Overflow

dropping constraint throws error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related