8

I created a PostgresQL table but I added an unnamed check constraint on one of the columns:

CREATE TABLE FOO
(
id serial primary key,
price_range smallint CHECK (price_range > 0),
url varchar(255)
);

Now I want to remove this constraint but I can't figure out how. The typical ALTER TABLE...DROP CONSTRAINT... needs a constraint_name but I don't have one.

I know there is an answer here but when I tried to determine the name of my check constraint by following the answer there:

SELECT *
FROM information_schema.constraint_table_usage
WHERE table_name = 'your_table'

All I got back was a single constraint whose constraint_name entry was foo pkey which refers to the primary key constraint and not my check on the price column. So that answer did not help me, unless I am missing something.

How can I drop this constraint without losing any data?

Thanks!

migrated from stackoverflow.com Jan 15 '15 at 10:05

This question came from our site for professional and enthusiast programmers.

10

The constraint has a name, whether you specified it or not.

From psql, \d foo will list all table constraints, along with their automatically assigned names.

You can also find these by querying the catalog tables directly:

SELECT conname
FROM pg_constraint
WHERE
  conrelid = 'foo'::regclass AND
  contype = 'c'

The table name can be schema-qualified if necessary (e.g. 'public.foo'::regclass).

contype = 'c' filters it down to CHECK constraints; the contype values for other constraint types are documented here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.