[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] How get column-wise table info from an arbitrary query?
From:       David Johnston <polobo () yahoo ! com>
Date:       2013-07-30 22:35:10
Message-ID: 1375223710890-5765681.post () n5 ! nabble ! com
[Download RAW message or body]

Gavin Flower-2 wrote
> Also, since I know what table 
> I'm looking at, it seemed redundant to also specify the table name as 
> part of the table's primary key!

I find this quite the opposite approach.  I know I am likely to use a
primary key as a foreign key so making it "externally friendly" makes a lot
of sense.  Where redundancy gets me is all the other functionally dependent
columns on the table and sometimes I'll relent and not prefix those since I
don't plan to join on them and their "table" qualifier can be done as-needed
when writing queries.

Examples for non-prefixing are things like invoice open/close dates and
sale/cost prices (I do work for retailers).  Joining two tables, each having
this kind of data, is uncommon and so leaving these attributes plain is
reasonable.  

Besides key fields I also prefix record timestamp fields (and similar) with
the table prefix since many/most tables can/should have a timestamp and if
you name every single timestamp column "tstamp" you can never use a NATURAL
JOIN - though you can still use USING(...) but in that case you still have
duplicate output column names.

It is not the difficult in most cases to distinguish between primary and
foreign keys - in pretty much any context.  Column order often suffices and
whether a column is used in a primary or foreign context is separate from
the meaning of said column data and meaning does not change so the name
should not either.

The really tricky part is that I prefer to abbreviate the more commonly used
table prefixes (and the really long ones) so generally either interpolation
or verbosity is needed for any given ID but it is a small price compared to
the sanity it provides.

David J.




--
View this message in context: \
http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765681.html
 Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]