Display of foreign keys in psql

Peter Eisentraut wrote:

I'm having trouble coming up with a sensible translation for the last line of
this:

peter=# \d test*
Table "public.test1"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"test1_pkey" PRIMARY KEY, btree (a)
Referenced by:
"test2_y_fkey" IN test2 FOREIGN KEY (y) REFERENCES test1(a)

Is there a magic reason why the IN is capitalized? (Maybe "from" would be
better anyway?)

Probably not. They were used to capitalizing "IN" for a subquery and it
carried over; should be lowercase.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> wrote:

Peter Eisentraut wrote:

Indexes:
"test1_pkey" PRIMARY KEY, btree (a)
Referenced by:
"test2_y_fkey" IN test2 FOREIGN KEY (y) REFERENCES test1(a)

Is there a magic reason why the IN is capitalized?

should be lowercase.

What about PRIMARY KEY, FOREIGN KEY, and REFERENCES?

Shouldn't these keywords be consistently capitalized (or not)?

-Kevin

Peter Eisentraut <peter_e@gmx.net> writes:

Referenced by:
"test2_y_fkey" IN test2 FOREIGN KEY (y) REFERENCES test1(a)

Is there a magic reason why the IN is capitalized? (Maybe "from" would be
better anyway?)

I think it was probably done to make it more visually distinct from the
adjacent identifiers, which would be lowercase more often than not.

Personally I'd suggest losing the word altogether; what's wrong with

Referenced by:
"test2_y_fkey" test2 FOREIGN KEY (y) REFERENCES test1(a)

Or use TABLE:

"test2_y_fkey" TABLE test2 FOREIGN KEY (y) REFERENCES test1(a)

regards, tom lane

On Wed, Jun 10, 2009 at 10:58 PM, Bruce Momjian<bruce@momjian.us> wrote:

Is there a magic reason why the IN is capitalized?  (Maybe "from" would be
better anyway?)

Probably not.  They were used to capitalizing "IN" for a subquery and it
carried over;   should be lowercase.

Well in that line everything that isn't part of the user's
expressions, columns, or table names is in all-caps. But I agree it
looks odd. I don't really have a better suggestion though.

Hm, maybe "in table foo" or "from table foo" would be better than just
having the one preposition alone.

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

2009/6/11 Peter Eisentraut <peter_e@gmx.net>:

Referenced by:
 "test2_y_fkey" IN test2 FOREIGN KEY (y) REFERENCES test1(a)

Is there a magic reason why the IN is capitalized?  (Maybe "from" would be
better anyway?)

Isn't "on" the conventional preposition to use here? I would think of
this as a foreign key "on" test2, referencing test1.

In the same way you would talk about having a primary key "on" a
table. Same goes for indexes, triggers, table constraints, etc. IMO

Cheers,
BJ

On Wed, Jun 10, 2009 at 11:04 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Or use TABLE:

 "test2_y_fkey" TABLE test2 FOREIGN KEY (y) REFERENCES test1(a)

Hm, one of the things a lot of people said they liked about the
existing list is that it was almost copy-pastable as the command to
recreate the constraint. If we use "TABLE" it might make sense to
reorder things so that it matches the command.

The command that would recreate this constraint is:

ALTER TABLE test2 ADD CONSTRAINT test2_y_fkey FOREIGN KEY (y)
REFERENCES test1(a);

So perhaps something like:

Referenced by:

TABLE test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a);

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

Greg Stark <stark@enterprisedb.com> writes:

So perhaps something like:

Referenced by:
TABLE test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a);

+1

... although making it *really* copy-and-pastable would require a bit
more attention to detail than I bet it's gotten. (Schema qualification
and double-quoting-at-need being the likely trouble spots.) Still,
we can fix that later if we have the basic appearance being compatible
with that goal.

regards, tom lane

On Wed, Jun 10, 2009 at 11:21 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Greg Stark <stark@enterprisedb.com> writes:

So perhaps something like:

Referenced by:
TABLE test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a);

+1

... although making it *really* copy-and-pastable would require a bit
more attention to detail than I bet it's gotten.  (Schema qualification
and double-quoting-at-need being the likely trouble spots.)  Still,
we can fix that later if we have the basic appearance being compatible
with that goal.

Even there the "TABLE" is kind of optional. It would stlil make sense as

Referenced by:
test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a)

and even like that it looks like it's the widest line in the printout

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

Greg Stark <stark@enterprisedb.com> writes:

On Wed, Jun 10, 2009 at 11:21 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Greg Stark <stark@enterprisedb.com> writes:

TABLE test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a);

+1

Even there the "TABLE" is kind of optional. It would stlil make sense as

Referenced by:
test2 CONSTRAINT test2_y_fkey FOREIGN KEY (y) REFERENCES test1(a)

I think that goes against our message style guidelines (46.3.9:
"When citing the name of an object, state what kind of object it is").
Not sure if brevity is worth a small chance of confusion.

regards, tom lane