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

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How get column-wise table info from an arbitrary query?
Date: 2013-07-30 19:26:04
Message-ID: 51F8134C.70307@archidevsys.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-07-30 19:27:00 Re: to_char with locale decimal separator
Previous Message bricklen 2013-07-30 17:48:58 Re: Alter table never finishes