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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
