Anonymous 1800
I have a question about this fiddle:
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=bccbc039e0c790b544c86abfca1c8a22
Hi,
Is there a way to alter this table to have a composite key consisting of both *id* and *value* columns instead of just a primary key on the *id* column. I'm having trouble because I don't know the name of the primary key constraint and I'm not familiar with dynamic SQL.
Any help would be appreciated.
Top Answer
Truilus
If you want, you can rely on the automatic naming pattern Postgres uses. For primary keys, this is always table_name_pkey unless you specified a constraint name.
So for the given table, the following is going to work:
alter table test drop constraint test_pkey;
This only stops working if your table name is longer than 58 characters
You can also use a SQL script to generate the needed SQL:
select format('alter table %I.%I drop constraint %I cascade', table_schema::text, table_name::text, constraint_name::text)
from information_schema.table_constraints
where table_schema = 'public'
and table_name = 'test'
and constraint_type = 'PRIMARY KEY';
In `psql` you could run the result of that using the `\g` meta command.
Or do everything with dynamic SQL:
do
$$
declare
l_sql text;
begin
select format('alter table %I.%I drop constraint %I cascade', table_schema::text, table_name::text, constraint_name::text)
into l_sql
from information_schema.table_constraints
where table_schema = 'public'
and table_name = 'test'
and constraint_type = 'PRIMARY KEY';
execute l_sql;
end;
$$
;
Obviously you need to adjust the table and schema name in the WHERE condition.