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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general