How do you drop an unnamed check constraint in a PostgresQL table?

Question

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!

Answer 1

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.

Answer 2

It doesn't matter if you specified a name or not, the constraint has a name. To figure out the "Constraint name" in Postgre SQL using pgAdmin4. The easiest way is to open the database first, then click "Schemas", then click "Tables" and select the table in which we have to make the change. Now a drop-down menu will open where you will see "Constraints". Double-click over the "Constraints" column and you will see a new drop-down menu with all the "Constraints" we created for the respective table. Now you can "right click" on the respective column name and then click "Properties". A new pop-up window will open in which we can see the name of the constraint under "General". The name we see there is the name of the constraint. Now you can copy that name and execute the code by pasting it. I hope this helps.

E.g: Alter TABLE foo DROP CONSTRAINT foo_price_range_check;