Thread: Dynamic constraint names in ALTER TABLE
Hi,
Where I work, we have a large deployment of software using PostgreSQL
database. We have been stuck on version 7.4.16 for a while now. I am
about to switch us to a 9.0.x.
One problem I'm running into, and I am hoping you can help me with,
given the constraints I have to work with, is our conversion
"scripts".
As our DB schema changes, we have a series of convert SQL scripts that
are executed through psql to alter tables, constraints, etc. This is
done at upgrade time. This has worked pretty well over the years for
us. However, what I notice is that, while in PG 7.4.x the constraints
(foreign keys) seem to take of the form "$1", "$2", etc., PG 9.x seems
to give them a more descriptive names. This behavior change breaks our
SQL convert scripts.
Is there anyway I can change our .sql files to make the the ALTER
TABLE <ADD|DROP> CONSTRAINT statements determine the constraint name
based on system catalog?
Here a simple example demonstrating my issue:
foo=# CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"employee_pkey" for table "employee"
CREATE TABLE
foo=# CREATE TABLE sales (seller INTEGER PRIMARY KEY, amount INTEGER,
FOREIGN KEY (seller) REFERENCES employee (id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"sales_pkey" for table "sales"
CREATE TABLE
In PostgreSQL 7.4.17:
foo=# \d sales
Table "public.sales"
Column | Type | Modifiers
--------+---------+-----------
seller | integer | not null
amount | integer |
Indexes:
"sales_pkey" PRIMARY KEY, btree (seller)
Foreign-key constraints:
"$1" FOREIGN KEY (seller) REFERENCES employee(id)
In PostgreSQL 9.0.3:
foo=# \d sales
Table "public.sales"
Column | Type | Modifiers
--------+---------+-----------
seller | integer | not null
amount | integer |
Indexes:
"sales_pkey" PRIMARY KEY, btree (seller)
Foreign-key constraints:
"sales_seller_fkey" FOREIGN KEY (seller) REFERENCES employee(id)
# If the conversion wanted to change the constraint to
# add ON DELETE CASCADE (simple example).
In PostgreSQL 7.4.17 our .sql convert script would say:
foo=# ALTER TABLE sales DROP CONSTRAINT "$1" ;
ALTER TABLE
foo=# ALTER TABLE sales ADD CONSTRAINT "$1" FOREIGN KEY (seller)
REFERENCES employee (id) ON DELETE CASCADE ;
ALTER TABLE
In PostgreSQL 9.0.3 our .sql convert script would need to say:
foo=# ALTER TABLE sales DROP CONSTRAINT "sales_seller_fkey" ;
ALTER TABLE
foo=# ALTER TABLE sales ADD CONSTRAINT "sales_seller_fkey" FOREIGN KEY
(seller) REFERENCES employee (id) ON DELETE CASCADE ;
ALTER TABLE
Is there any way the .sql scripts could make use of this query to get
the foreign key name from pg_constraint table, regardless of PG
version (7.4.x or 9.x)?
foo=# SELECT conname FROM pg_constraint JOIN pg_class ON
(conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] =
1 AND contype='f';
In PostgreSQL 7.4.17:
conname
---------
$1
(1 row)
In PostgreSQL 9.0.3:
conname
-------------------
sales_seller_fkey
(1 row)
Thanks for reading,
--patrick
On Monday, September 19, 2011 5:10:45 pm patrick keshishian wrote: > Hi, > > > > Is there any way the .sql scripts could make use of this query to get > the foreign key name from pg_constraint table, regardless of PG > version (7.4.x or 9.x)? Use the information schema? As example: http://www.postgresql.org/docs/7.4/static/infoschema-table-constraints.html http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constraints.html > > foo=# SELECT conname FROM pg_constraint JOIN pg_class ON > (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = > 1 AND contype='f'; > > In PostgreSQL 7.4.17: > conname > --------- > $1 > (1 row) > > > In PostgreSQL 9.0.3: > conname > ------------------- > sales_seller_fkey > (1 row) > > > Thanks for reading, > --patrick -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Sep 19, 2011 at 6:08 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Monday, September 19, 2011 5:10:45 pm patrick keshishian wrote: >> Hi, >> >> Is there any way the .sql scripts could make use of this query to get >> the foreign key name from pg_constraint table, regardless of PG >> version (7.4.x or 9.x)? > > Use the information schema? As example: > http://www.postgresql.org/docs/7.4/static/infoschema-table-constraints.html > http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constraints.html I think you you missed the intent of my question; unless I've missed depth of your answer. The question wasn't where does one find the name of the constraint. My example demonstrated that I knew how to get that value. The question, however, is how do you get that in an ALTER TABLE statement? A sub-select doesn't seem to work. e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; That does not work. I can generate the SQL statements using SELECTs, output (\o) them to a /tmp/really-hacky-way-to-do-this.sql files, then read (\i) them into psql, but as the file name says, this is getting perverse. --patrick >> foo=# SELECT conname FROM pg_constraint JOIN pg_class ON >> (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = >> 1 AND contype='f'; >> >> In PostgreSQL 7.4.17: >> conname >> --------- >> $1 >> (1 row) >> >> >> In PostgreSQL 9.0.3: >> conname >> ------------------- >> sales_seller_fkey >> (1 row) >> >> >> Thanks for reading, >> --patrick > > -- > Adrian Klaver > adrian.klaver@gmail.com >
Hi, On 20 September 2011 13:09, patrick keshishian <pkeshish@gmail.com> wrote: > e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM > pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE > pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; You have to build query in different way: psql ... -A -t -c "SELECT 'ALTER TABLE sales DROP CONSTRAINT ' || conname || ';' FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 1 AND contype='f'" Finally, you can save it in to file or pipe it to another psql: psql ... -A -t -c '....' | psql ... -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
patrick keshishian <pkeshish@gmail.com> writes:
> The question wasn't where does one find the name of the constraint. My
> example demonstrated that I knew how to get that value. The question,
> however, is how do you get that in an ALTER TABLE statement?
You'd need to construct the ALTER statement as a string value and then
EXECUTE it, using a plpgsql function. (In 9.0 you could use a DO
command, but if you want this to also work in 7.4, it'll have to be a
plain old function.)
regards, tom lane
2011/9/19 Ondrej Ivanič <ondrej.ivanic@gmail.com>: > Hi, > > On 20 September 2011 13:09, patrick keshishian <pkeshish@gmail.com> wrote: >> e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM >> pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE >> pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; > > You have to build query in different way: > > psql ... -A -t -c "SELECT 'ALTER TABLE sales DROP CONSTRAINT ' || > conname || ';' FROM pg_constraint JOIN pg_class ON > (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = > 1 AND contype='f'" > > Finally, you can save it in to file or pipe it to another psql: > psql ... -A -t -c '....' | psql ... this is no different than my save to a file (\o) and read it back (\i) way. --patrick
On Mon, Sep 19, 2011 at 8:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > patrick keshishian <pkeshish@gmail.com> writes: >> The question wasn't where does one find the name of the constraint. My >> example demonstrated that I knew how to get that value. The question, >> however, is how do you get that in an ALTER TABLE statement? > > You'd need to construct the ALTER statement as a string value and then > EXECUTE it, using a plpgsql function. (In 9.0 you could use a DO > command, but if you want this to also work in 7.4, it'll have to be a > plain old function.) OK. Thanks all. --patrick
On Monday, September 19, 2011 8:09:04 pm patrick keshishian wrote: > On Mon, Sep 19, 2011 at 6:08 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > > On Monday, September 19, 2011 5:10:45 pm patrick keshishian wrote: > >> Hi, > >> > >> Is there any way the .sql scripts could make use of this query to get > >> the foreign key name from pg_constraint table, regardless of PG > >> version (7.4.x or 9.x)? > > > > Use the information schema? As example: > > http://www.postgresql.org/docs/7.4/static/infoschema-table-constraints.ht > > ml > > http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constrai > > nts.html > > I think you you missed the intent of my question; unless I've missed > depth of your answer. My mistake. I misread the question and I thought you where looking for a way to get the information without using the system catalogs. > > The question wasn't where does one find the name of the constraint. My > example demonstrated that I knew how to get that value. The question, > however, is how do you get that in an ALTER TABLE statement? A > sub-select doesn't seem to work. > > e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM > pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE > pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; > > That does not work. > > I can generate the SQL statements using SELECTs, output (\o) them to a > /tmp/really-hacky-way-to-do-this.sql files, then read (\i) them into > psql, but as the file name says, this is getting perverse. > Just out of curiosity, what do you do if there is more than one constraint on a table and you want to apply different changes? -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Sep 20, 2011 at 7:36 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Monday, September 19, 2011 8:09:04 pm patrick keshishian wrote: >> On Mon, Sep 19, 2011 at 6:08 PM, Adrian Klaver <adrian.klaver@gmail.com> > wrote: >> > On Monday, September 19, 2011 5:10:45 pm patrick keshishian wrote: >> >> Hi, >> >> >> >> Is there any way the .sql scripts could make use of this query to get >> >> the foreign key name from pg_constraint table, regardless of PG >> >> version (7.4.x or 9.x)? >> > >> > Use the information schema? As example: >> > http://www.postgresql.org/docs/7.4/static/infoschema-table-constraints.ht >> > ml >> > http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constrai >> > nts.html >> >> I think you you missed the intent of my question; unless I've missed >> depth of your answer. > > My mistake. I misread the question and I thought you where looking for a way to > get the information without using the system catalogs. > >> >> The question wasn't where does one find the name of the constraint. My >> example demonstrated that I knew how to get that value. The question, >> however, is how do you get that in an ALTER TABLE statement? A >> sub-select doesn't seem to work. >> >> e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM >> pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE >> pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; >> >> That does not work. >> >> I can generate the SQL statements using SELECTs, output (\o) them to a >> /tmp/really-hacky-way-to-do-this.sql files, then read (\i) them into >> psql, but as the file name says, this is getting perverse. >> > > > Just out of curiosity, what do you do if there is more than one constraint on a > table and you want to apply different changes? You mean in a situation like this: foo=# CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT); foo=# CREATE TABLE product (id INTEGER PRIMARY KEY, description TEXT) ; foo=# CREATE TABLE sales (seller INTEGER, amount INTEGER, item INTEGER, FOREIGN KEY (seller) REFERENCES employee (id), FOREIGN KEY (item) REFERENCES product (id)); foo=# \d sales Table "public.sales" Column | Type | Modifiers --------+---------+----------- seller | integer | amount | integer | item | integer | Foreign-key constraints: "sales_item_fkey" FOREIGN KEY (item) REFERENCES product(id) "sales_seller_fkey" FOREIGN KEY (seller) REFERENCES employee(id) The conkey field in the pg_constraint table helps you choose the specific constraint you are referring to. i.e., when I am looking for the FOREIGN KEY referenced by seller (column 1) conkey[1] = 1: foo=# SELECT conname FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 1 AND contype='f'; conname ------------------- sales_seller_fkey (1 row) FOREIGN KEY referenced by item (column 3) conkey[1] = 3: foo=# SELECT conname FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 3 AND contype='f'; conname ----------------- sales_item_fkey (1 row) Best, --patrick