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

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.