Thread: How get column-wise table info from an arbitrary query?

On 05/12/2012 01:04, Kenneth Tilton wrote:
> I am porting from MySQL some code that has to take an arbitrary query
> involving joins and build up a dictionary (in an HLL talking to Postgres
> over a socket) where each column name will be the key. The catch is that
> there will be duplicate entries where two joined tables have the same
> column such as "id", so I have to get the source table for each column.
> Here is a sample query:
>
>    select * from providers p inner join provider_types pt on pt.id
> <http://pt.id> = p.provider_type_id;
>
> I actually figured out how to get the table OID which would suffice, but
> I am porting MySQL code that could get fully qualified column names
> including a table alias if that were used. We allow other code to look
> up values in the dictionary with the alias as a prefix as a convenience,
> eg. "p.id <http://p.id>" or "pt.id <http://pt.id>".

Maybe I'm misunderstanding, but why don't you just give the columns an
alias directly? -

   select p.id as p_id, pt.id as pt_id, ....

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 05/12/2012 01:11, Raymond O'Donnell wrote:
> On 05/12/2012 01:04, Kenneth Tilton wrote:
>> I am porting from MySQL some code that has to take an arbitrary query
>> involving joins and build up a dictionary (in an HLL talking to Postgres
>> over a socket) where each column name will be the key. The catch is that
>> there will be duplicate entries where two joined tables have the same
>> column such as "id", so I have to get the source table for each column.
>> Here is a sample query:
>>
>>    select * from providers p inner join provider_types pt on pt.id
>> <http://pt.id> = p.provider_type_id;
>>
>> I actually figured out how to get the table OID which would suffice, but
>> I am porting MySQL code that could get fully qualified column names
>> including a table alias if that were used. We allow other code to look
>> up values in the dictionary with the alias as a prefix as a convenience,
>> eg. "p.id <http://p.id>" or "pt.id <http://pt.id>".
>
> Maybe I'm misunderstanding, but why don't you just give the columns an
> alias directly? -
>
>    select p.id as p_id, pt.id as pt_id, ....

Sorry, I *am* misunderstanding - I missed the "arbitrary" bit. Too late
at night to be working...

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

I really dislike ambiguous column names across tables in a database. Use the
convention [tablename]_id for each id so every column name is self
describing. That way you can:

select * from providers inner join provider_types using(provider_type_id);

No need for table aliases, column aliases and no ambiguity.


Kenneth Tilton-2 wrote
> Is there any way on an arbitrary query to determine column names qualified
> by table aliases?

You could use a query like this to get a list of fully qualified column
names:

SELECT pg_tables.tablename||'.'||columns.column_name as columnname
FROM pg_tables,information_schema.columns columns
WHERE pg_tables.tablename=columns.table_name AND
pg_tables.schemaname='public'
ORDER by pg_tables.tablename;

you can amend that query to only look for columns of certain types, tie to
primary keys of tables or indexes. PostgreSQL is really rather helpful in
that regard.



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

I use 'id' for the primary key, and [tablename]_id for each foreign key,
I always qualify my column references in SQL, but I would never use
SELECT * when selecting from more than one table.


Cheers,
Gavin

On 30/07/13 21:41, sidthegeek wrote:
> I really dislike ambiguous column names across tables in a database. Use the
> convention [tablename]_id for each id so every column name is self
> describing. That way you can:
>
> select * from providers inner join provider_types using(provider_type_id);
>
> No need for table aliases, column aliases and no ambiguity.
>
>
> Kenneth Tilton-2 wrote
>> Is there any way on an arbitrary query to determine column names qualified
>> by table aliases?
> You could use a query like this to get a list of fully qualified column
> names:
>
> SELECT pg_tables.tablename||'.'||columns.column_name as columnname
> FROM pg_tables,information_schema.columns columns
> WHERE pg_tables.tablename=columns.table_name AND
> pg_tables.schemaname='public'
> ORDER by pg_tables.tablename;
>
> you can amend that query to only look for columns of certain types, tie to
> primary keys of tables or indexes. PostgreSQL is really rather helpful in
> that regard.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765601.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

Gavin Flower-2 wrote
> I use 'id' for the primary key, and [tablename]_id for each foreign key,
> I always qualify my column references in SQL, but I would never use
> SELECT * when selecting from more than one table.
>
>
> Cheers,
> Gavin
>
> On 30/07/13 21:41, sidthegeek wrote:
>> I really dislike ambiguous column names across tables in a database. Use
>> the
>> convention [tablename]_id for each id so every column name is self
>> describing. That way you can:
>>
>> select * from providers inner join provider_types
>> using(provider_type_id);
>>
>> No need for table aliases, column aliases and no ambiguity.
>>

I'm in the "prefix the id column" camp.  I do not use "ORM" middle-ware so
that may be a reason I do not have any difficulties but one of the big
advantages to table-prefixing generic column names is that you can then make
the assumption that any two columns with the same name represent the same
data.  It does make "SELECT *" more useful when running interactive queries
and, more importantly, it makes using NATURAL JOIN and USING (...) much
easier - and I hate using ON (...) to perform a join (and I never use the
"FROM a, b WHERE a = b" cartesian join construct).

To the original question introspection of dynamic SQL is not a strong point
of PostgreSQL (cannot speak to other products).  Given the nature of how a
query works and the fact that columns can be created on-the-fly (i.e., not
belonging to any schema) this is not surprising.  You could try running and
capturing the output of EXPLAIN with various options like JSON and VERBOSE
and store that - it depends on your use-case.

David J.



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

On Tue, Jul 30, 2013 at 4:57 PM, David Johnston <polobo@yahoo.com> wrote:
> I'm in the "prefix the id column" camp.  I do not use "ORM" middle-ware so
> that may be a reason I do not have any difficulties but one of the big
> advantages to table-prefixing generic column names is that you can then make
> the assumption that any two columns with the same name represent the same
> data.  It does make "SELECT *" more useful when running interactive queries
> and, more importantly, it makes using NATURAL JOIN and USING (...) much
> easier - and I hate using ON (...) to perform a join (and I never use the
> "FROM a, b WHERE a = b" cartesian join construct).

I would say: most of all it completely defeats useful text searching.
Any ORM (or anything else) that enforces a primary key integer column
named 'id' is bad technology and should be avoided.  Schema should
define the 'object model' not the other way around; I'd estimate that
around 20-30% of my life's work has been cleaning up the various dreck
left around by those who fail to grasp that basic principle.

merlin

Gavin Flower-2 wrote

I use 'id' for the primary key, and [tablename]_id for each foreign key, 
I always qualify my column references in SQL, but I would never use 
SELECT * when selecting from more than one table.


Cheers,
Gavin

On 30/07/13 21:41, sidthegeek wrote:

I really dislike ambiguous column names across tables in a database. Use
the
convention [tablename]_id for each id so every column name is self
describing. That way you can:

select * from providers inner join provider_types
using(provider_type_id);

No need for table aliases, column aliases and no ambiguity.

I'm in the "prefix the id column" camp.  I do not use "ORM" middle-ware so
that may be a reason I do not have any difficulties but one of the big
advantages to table-prefixing generic column names is that you can then make
the assumption that any two columns with the same name represent the same
data.  It does make "SELECT *" more useful when running interactive queries
and, more importantly, it makes using NATURAL JOIN and USING (...) much
easier - and I hate using ON (...) to perform a join (and I never use the
"FROM a, b WHERE a = b" cartesian join construct).

To the original question introspection of dynamic SQL is not a strong point
of PostgreSQL (cannot speak to other products).  Given the nature of how a
query works and the fact that columns can be created on-the-fly (i.e., not
belonging to any schema) this is not surprising.  You could try running and
capturing the output of EXPLAIN with various options like JSON and VERBOSE
and store that - it depends on your use-case.

David J.



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

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.