primary key display in psql
When you look at a table definition with psql \d, one of the arguably
most important pieces of information -- the primary key -- is hidden
somewhere below under "indexes":
Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer | not null
Indexes:
"test2_pkey" PRIMARY KEY, btree (a, b)
I think we could easily improve that by having it look something like
this instead:
Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------
a | integer | PK
b | integer | PK
Indexes:
"test2_pkey" PRIMARY KEY, btree (a, b)
Since there can only be one primary key, this should be unambiguous.
I don't have time to code this up right now, but maybe someone feels
inspired. What do you think?
Peter Eisentraut <peter_e@gmx.net> writes:
I think we could easily improve that by having it look something like
this instead:
Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------
a | integer | PK
b | integer | PK
Indexes:
"test2_pkey" PRIMARY KEY, btree (a, b)
Spelling out "primary key" would seem to be more in keeping with existing
entries in that column, eg we have "not null" not "NN".
I think this is a sensible proposal for a single-column PK, but am less
sure that it makes sense for multi-col. The modifiers column is
intended to describe column constraints; which a multi-col PK is not,
by definition.
regards, tom lane
On Wed, Jan 13, 2010 at 4:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
I think we could easily improve that by having it look something like
this instead:Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------
a | integer | PK
b | integer | PK
Indexes:
"test2_pkey" PRIMARY KEY, btree (a, b)Spelling out "primary key" would seem to be more in keeping with existing
entries in that column, eg we have "not null" not "NN".I think this is a sensible proposal for a single-column PK, but am less
sure that it makes sense for multi-col. The modifiers column is
intended to describe column constraints; which a multi-col PK is not,
by definition.
Yeah, IIRC, MySQL shows PRI for each column of a multi-column primary
key, and I think it's horribly confusing. I wouldn't even be in favor
of doing this just for the single-column case, on the grounds that it
makes the single and multiple column cases asymmetrical. IMO, the \d
output has too many bells and whistles already; the last thing we
should do is add more.
...Robert
On Wed, Jan 13, 2010 at 05:03:33PM -0500, Robert Haas wrote:
On Wed, Jan 13, 2010 at 4:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Spelling out "primary key" would seem to be more in keeping with existing
entries in that column, eg we have "not null" not "NN".I think this is a sensible proposal for a single-column PK, but am less
sure that it makes sense for multi-col. �The modifiers column is
intended to describe column constraints; which a multi-col PK is not,
by definition.Yeah, IIRC, MySQL shows PRI for each column of a multi-column primary
key, and I think it's horribly confusing. I wouldn't even be in favor
of doing this just for the single-column case, on the grounds that it
makes the single and multiple column cases asymmetrical. IMO, the \d
output has too many bells and whistles already; the last thing we
should do is add more.
How about spelling it as so:
� � Table "public.test"
�Column | �Type � | Modifiers
--------+---------+-----------
�a � � �| integer | primary key
�b � � �| integer |
Indexes:
� � "test1_pkey" PRIMARY KEY, btree (a)
� � Table "public.test2"
�Column | �Type � | Modifiers
--------+---------+-----------
�a � � �| integer | primary key (compound)
�b � � �| integer | primary key (compound)
Indexes:
� � "test2_pkey" PRIMARY KEY, btree (a, b)
As to Tom's point that a compound primary key is a table level
restriction, by definition, participating in such a key is still a
restriction on what values that column can take. When introspecting
someone else's schema, with a very wide table, seeing '(compound)'
is a nice strong hint to go looking for the other members of the PK.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE