Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux