From: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | psql's \d display of unique index vs. constraint |
Date: | 2010-04-09 22:01:34 |
Message-ID: | t2i4ec1cf761004091501r6b1a27e0q7d6dd63509cd7744@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Hi all,
I often come across tables with either a unique index or a unique
constraint on them, and psql isn't helpful at showing the difference
between the two. Normally, I don't care which is which, except for
when I have to manually drop and recreate the index or constraint to
speed up a bulk load.
Consider the following two tables, con_tbl and idx_tbl:
CREATE TABLE con_tbl (
pkid int PRIMARY KEY,
identifier text
);
ALTER TABLE con_tbl
ADD CONSTRAINT "con_tbl_identifier_key" UNIQUE ("identifier");
CREATE TABLE idx_tbl (
pkid int PRIMARY KEY,
identifier text
);
CREATE UNIQUE INDEX "idx_tbl_identifier_key" ON "idx_tbl" ("identifier");
If you use psql's \d command to describe the two tables, you'll see:
<snip>
Indexes:
"idx_tbl_pkey" PRIMARY KEY, btree (pkid)
"idx_tbl_identifier_key" UNIQUE, btree (identifier)
<snip>
Indexes:
"con_tbl_pkey" PRIMARY KEY, btree (pkid)
"con_tbl_identifier_key" UNIQUE, btree (identifier)
These two displays are exactly the same, except for the names I chose.
However, if you try either of:
DROP INDEX "con_tbl_identifier_key";
ALTER TABLE "idx_tbl"
DROP CONSTRAINT "idx_tbl_identifier_key";
you'll see both of these commands fail -- you have to know which was
declared as a constraint, and which as an index, in order to know to
use:
ALTER TABLE "con_tbl"
DROP CONSTRAINT "con_tbl_identifier_key";
DROP INDEX "idx_tbl_identifier_key";
Unless there's a simple psql command or display option I'm missing
(\d+ doesn't help), I think it would be better if the \d display of
"idx_tbl_identifier_key" was kept as-is, and the display for
"con_tbl_identifier_key" was presented under a separate "Unique
Constraints" section or a similar heading. Even better would be
allowing either ALTER TABLE ... DROP CONSTRAINT or DROP INDEX to work
regardless of how the unique index or constraint was declared, though
perhaps that would be more work.
Tested with a recent psql:
$ psql -V
psql (PostgreSQL) 9.0devel
contains support for command-line editing
Thanks
Josh
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2010-04-10 09:54:46 | @> and <@ (contains and is contained by) operations on large arrays |
Previous Message | Sam Mason | 2010-04-09 21:03:19 | Re: Can not connect remotely |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-04-09 22:05:32 | Re: GSoC - proposal - Materialized Views in PostgreSQL |
Previous Message | Robert Haas | 2010-04-09 22:00:27 | Re: extended operator classes vs. type interfaces |