pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddistinguishing identical columns after joins
8+ messages / 4 participants
[nested] [flat]
* distinguishing identical columns after joins
@ 2011-03-01 19:47 S G <sgennaria2@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: S G @ 2011-03-01 19:47 UTC (permalink / raw)
To: pgsql-sql
This question is particularly geared towards self-joins, but can apply
to any join where the tables involved have any identical column names.
Aside from explicit column references, is there any way to pull all
columns (*) from each table in a join and quickly append/prepend some
identifier to distinguish them from each other? For example, table t1
contains columns named col1 and col2:
SELECT
*
FROM
t1 AS a
INNER JOIN
t1 AS b
ON
a.col1 = b.col1
would yield a result set with column names: col1, col2, col1, col2.
I'm looking for something that would automatically rename the columns
like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist?
It's not such a big deal in this example, but it can be quite tedious
to explicitly reference and rename every single column for such joins
when the tables involved have a very large number of columns.
I would beg for the same functionality when expanding compound
datatypes. For example, a compound datatype cd1 exists with fields
named f1 and f2:
SELECT
((value1, value2)::cd1).* AS a
normally produces a result set with column names: f1, f2. I'm looking
for something that would produce column names: a_f1, a_f2.
Thanks!
sg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-01 19:53 Rob Sargent <robjsargent@gmail.com>
parent: S G <sgennaria2@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rob Sargent @ 2011-03-01 19:53 UTC (permalink / raw)
To: pgsql-sql
On 03/01/2011 12:47 PM, S G wrote:
> This question is particularly geared towards self-joins, but can apply
> to any join where the tables involved have any identical column names.
> Aside from explicit column references, is there any way to pull all
> columns (*) from each table in a join and quickly append/prepend some
> identifier to distinguish them from each other? For example, table t1
> contains columns named col1 and col2:
>
> SELECT
> *
> FROM
> t1 AS a
> INNER JOIN
> t1 AS b
> ON
> a.col1 = b.col1
>
> would yield a result set with column names: col1, col2, col1, col2.
> I'm looking for something that would automatically rename the columns
> like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist?
> It's not such a big deal in this example, but it can be quite tedious
> to explicitly reference and rename every single column for such joins
> when the tables involved have a very large number of columns.
>
> I would beg for the same functionality when expanding compound
> datatypes. For example, a compound datatype cd1 exists with fields
> named f1 and f2:
>
> SELECT
> ((value1, value2)::cd1).* AS a
>
> normally produces a result set with column names: f1, f2. I'm looking
> for something that would produce column names: a_f1, a_f2.
>
> Thanks!
> sg
>
select a.col1 as a_col1 etc doesn't do it for you?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-01 22:13 S G <sgennaria2@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: S G @ 2011-03-01 22:13 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> On 03/01/2011 12:47 PM, S G wrote:
>> This question is particularly geared towards self-joins, but can apply
>> to any join where the tables involved have any identical column names.
>> Aside from explicit column references, is there any way to pull all
>> columns (*) from each table in a join and quickly append/prepend some
>> identifier to distinguish them from each other? For example, table t1
>> contains columns named col1 and col2:
>>
>> SELECT
>> *
>> FROM
>> t1 AS a
>> INNER JOIN
>> t1 AS b
>> ON
>> a.col1 = b.col1
>>
>> would yield a result set with column names: col1, col2, col1, col2.
>> I'm looking for something that would automatically rename the columns
>> like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist?
>> It's not such a big deal in this example, but it can be quite tedious
>> to explicitly reference and rename every single column for such joins
>> when the tables involved have a very large number of columns.
>>
>> I would beg for the same functionality when expanding compound
>> datatypes. For example, a compound datatype cd1 exists with fields
>> named f1 and f2:
>>
>> SELECT
>> ((value1, value2)::cd1).* AS a
>>
>> normally produces a result set with column names: f1, f2. I'm looking
>> for something that would produce column names: a_f1, a_f2.
>>
>> Thanks!
>> sg
>>
> select a.col1 as a_col1 etc doesn't do it for you?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Rob, what you wrote certainly does work. But suppose you had to do
that for a join with 50 columns in each table, and you really needed
to see all those columns show up in the final result set, and
furthermore, you needed to be able to identify each one uniquely in
the final result set. Explicit renaming works, but it's tedious.
Call me lazy. I'm hoping a column-renaming shortcut exists that works
with the "SELECT *" concept.
If such a shortcut doesn't exist, I believe it easily could exist
utilizing the following syntax:
SELECT
(a).* AS a_,
(b).* AS b_
FROM
t1 AS a
INNER JOIN
t1 AS b
ON
a.col1 = b.col1
which currently discards the AS identifiers and defaults to the column
names as identified in their respective tables. Though implementing
this is another issue altogether... I'm just asking if such a shortcut
already exists.
Thanks!
sg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-01 23:00 Rob Sargent <robjsargent@gmail.com>
parent: S G <sgennaria2@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Rob Sargent @ 2011-03-01 23:00 UTC (permalink / raw)
To: S G <sgennaria2@gmail.com>; +Cc: pgsql-sql
On 03/01/2011 03:13 PM, S G wrote:
> On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>>
>>
>> On 03/01/2011 12:47 PM, S G wrote:
>>> This question is particularly geared towards self-joins, but can apply
>>> to any join where the tables involved have any identical column names.
>>> Aside from explicit column references, is there any way to pull all
>>> columns (*) from each table in a join and quickly append/prepend some
>>> identifier to distinguish them from each other? For example, table t1
>>> contains columns named col1 and col2:
>>>
>>> SELECT
>>> *
>>> FROM
>>> t1 AS a
>>> INNER JOIN
>>> t1 AS b
>>> ON
>>> a.col1 = b.col1
>>>
>>> would yield a result set with column names: col1, col2, col1, col2.
>>> I'm looking for something that would automatically rename the columns
>>> like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist?
>>> It's not such a big deal in this example, but it can be quite tedious
>>> to explicitly reference and rename every single column for such joins
>>> when the tables involved have a very large number of columns.
>>>
>>> I would beg for the same functionality when expanding compound
>>> datatypes. For example, a compound datatype cd1 exists with fields
>>> named f1 and f2:
>>>
>>> SELECT
>>> ((value1, value2)::cd1).* AS a
>>>
>>> normally produces a result set with column names: f1, f2. I'm looking
>>> for something that would produce column names: a_f1, a_f2.
>>>
>>> Thanks!
>>> sg
>>>
>> select a.col1 as a_col1 etc doesn't do it for you?
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
> Rob, what you wrote certainly does work. But suppose you had to do
> that for a join with 50 columns in each table, and you really needed
> to see all those columns show up in the final result set, and
> furthermore, you needed to be able to identify each one uniquely in
> the final result set. Explicit renaming works, but it's tedious.
> Call me lazy. I'm hoping a column-renaming shortcut exists that works
> with the "SELECT *" concept.
>
> If such a shortcut doesn't exist, I believe it easily could exist
> utilizing the following syntax:
>
> SELECT
> (a).* AS a_,
> (b).* AS b_
> FROM
> t1 AS a
> INNER JOIN
> t1 AS b
> ON
> a.col1 = b.col1
>
> which currently discards the AS identifiers and defaults to the column
> names as identified in their respective tables. Though implementing
> this is another issue altogether... I'm just asking if such a shortcut
> already exists.
>
> Thanks!
> sg
I suspected this was the tack you were taking and would be mildly
surprised if it hasn't been requested before so I suspect some wise soul
will put us in the right direction.
But I still wonder it isn't a receiver/UI issue. Does your reader know
the meaning of "a_" vs "b_" in a non-trivial self join? Wouldn't you
rather have the output as a_col1, b_col1, a_col2, b_col2 ... for easy
comparison. And who can make sense of a 100 column results set anyway?:)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-01 23:37 Stephen Cook <sclists@gmail.com>
parent: S G <sgennaria2@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Stephen Cook @ 2011-03-01 23:37 UTC (permalink / raw)
To: S G <sgennaria2@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
In times like these, I usually write a query using
information_schema.columns to generate the column list:
SELECT ordinal_position,
1 AS table_instance,
'a.' || column_name || ' AS ' || column_name || '_a,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_here'
UNION ALL
SELECT ordinal_position,
2 AS table_instance,
'b.' || column_name || ' AS ' || column_name || '_b,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_here'
ORDER BY table_instance,
ordinal_position;
Or something along those lines, and copy-and-paste the results into the
query. It's quicker than typing them all out once you hit a certain
number of columns, and certainly less typo-prone.
It's not the shortcut you were thinking of but it works.
On 3/1/2011 5:13 PM, S G wrote:
> Rob, what you wrote certainly does work. But suppose you had to do
> that for a join with 50 columns in each table, and you really needed
> to see all those columns show up in the final result set, and
> furthermore, you needed to be able to identify each one uniquely in
> the final result set. Explicit renaming works, but it's tedious.
> Call me lazy. I'm hoping a column-renaming shortcut exists that works
> with the "SELECT *" concept.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-01 23:44 Lee Hachadoorian <lee.hachadoorian@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Lee Hachadoorian @ 2011-03-01 23:44 UTC (permalink / raw)
To: pgsql-sql
On 03/01/2011 06:00 PM, Rob Sargent wrote:
>
> On 03/01/2011 03:13 PM, S G wrote:
>> On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>>>
>>> On 03/01/2011 12:47 PM, S G wrote:
>>>> This question is particularly geared towards self-joins, but can apply
>>>> to any join where the tables involved have any identical column names.
>>>> Aside from explicit column references, is there any way to pull all
>>>> columns (*) from each table in a join and quickly append/prepend some
>>>> identifier to distinguish them from each other? For example, table t1
>>>> contains columns named col1 and col2:
>>>>
>>>> SELECT
>>>> *
>>>> FROM
>>>> t1 AS a
>>>> INNER JOIN
>>>> t1 AS b
>>>> ON
>>>> a.col1 = b.col1
>>>>
>>>> would yield a result set with column names: col1, col2, col1, col2.
>>>> I'm looking for something that would automatically rename the columns
>>>> like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist?
>>>> It's not such a big deal in this example, but it can be quite tedious
>>>> to explicitly reference and rename every single column for such joins
>>>> when the tables involved have a very large number of columns.
>>>>
>>>> I would beg for the same functionality when expanding compound
>>>> datatypes. For example, a compound datatype cd1 exists with fields
>>>> named f1 and f2:
>>>>
>>>> SELECT
>>>> ((value1, value2)::cd1).* AS a
>>>>
>>>> normally produces a result set with column names: f1, f2. I'm looking
>>>> for something that would produce column names: a_f1, a_f2.
>>>>
>>>> Thanks!
>>>> sg
>>>>
>>> select a.col1 as a_col1 etc doesn't do it for you?
>>>
>>> --
>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>>
>> Rob, what you wrote certainly does work. But suppose you had to do
>> that for a join with 50 columns in each table, and you really needed
>> to see all those columns show up in the final result set, and
>> furthermore, you needed to be able to identify each one uniquely in
>> the final result set. Explicit renaming works, but it's tedious.
>> Call me lazy. I'm hoping a column-renaming shortcut exists that works
>> with the "SELECT *" concept.
>>
>> If such a shortcut doesn't exist, I believe it easily could exist
>> utilizing the following syntax:
>>
>> SELECT
>> (a).* AS a_,
>> (b).* AS b_
>> FROM
>> t1 AS a
>> INNER JOIN
>> t1 AS b
>> ON
>> a.col1 = b.col1
>>
>> which currently discards the AS identifiers and defaults to the column
>> names as identified in their respective tables. Though implementing
>> this is another issue altogether... I'm just asking if such a shortcut
>> already exists.
>>
>> Thanks!
>> sg
> I suspected this was the tack you were taking and would be mildly
> surprised if it hasn't been requested before so I suspect some wise soul
> will put us in the right direction.
>
> But I still wonder it isn't a receiver/UI issue. Does your reader know
> the meaning of "a_" vs "b_" in a non-trivial self join? Wouldn't you
> rather have the output as a_col1, b_col1, a_col2, b_col2 ... for easy
> comparison. And who can make sense of a 100 column results set anyway?:)
>
I don't know if someone has come up with a workaround, but to begin with
note that the docs specify that when using the * "it is not possible to
specify new names with AS; the output column names will be the same as
the table columns' names."
Off the cuff, a possible workaround would be to create multiple views of
your table that rename the columns, i.e.
CREATE VIEW vw_a_t1 AS
SELECT
col1 AS a_col1, col2 AS a_col2, …
FROM
t1;
CREATE VIEW vw_b_t1 AS
SELECT
col1 AS b_col1, col2 AS b_col2, …
FROM
t1;
Then you would do your select as
SELECT *
FROM vw_a_t1 JOIN vw_b_t1 ON a_col1 = b_col1;
If you were often self-joining the table 3 or more times, you would
obviously have to create views vw_c_t1, vw_d_t1, etc. If you need to do
this for several tables, you might be able to create a function to
create the views. The function would take a table name and the desired
prefix as parameters and programatically construct, then EXECUTE the
CREATE VIEW statement.
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-02 13:45 S G <sgennaria2@gmail.com>
parent: Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: S G @ 2011-03-02 13:45 UTC (permalink / raw)
To: pgsql-sql
>> But I still wonder it isn't a receiver/UI issue. Does your reader know
>> the meaning of "a_" vs "b_" in a non-trivial self join? Wouldn't you
>> rather have the output as a_col1, b_col1, a_col2, b_col2 ... for easy
>> comparison. And who can make sense of a 100 column results set anyway?:)
Rob, I agree it's a doozy =) Regarding "a_" etc, I meant it as a
piece of some sort of underlying append/prepend operation for the
names in order to get the full names to display like you listed:
"a_col1" etc. As for my UI, I'm using PGAdmin since I haven't found
any other tools that let me retrieve and scroll over huge datasets as
easily as it does. Perhaps another UI might have some built-in
feature for this, but I haven't found it and I would still choose
PGAdmin for its ability to display huge datasets better.
> Off the cuff, a possible workaround would be to create multiple views of
> your table that rename the columns, i.e.
>
> CREATE VIEW vw_a_t1 AS
> SELECT
> col1 AS a_col1, col2 AS a_col2, …
> FROM
> t1;
>
> CREATE VIEW vw_b_t1 AS
> SELECT
> col1 AS b_col1, col2 AS b_col2, …
> FROM
> t1;
>
> Then you would do your select as
>
> SELECT *
> FROM vw_a_t1 JOIN vw_b_t1 ON a_col1 = b_col1;
>
> If you were often self-joining the table 3 or more times, you would
> obviously have to create views vw_c_t1, vw_d_t1, etc. If you need to do
> this for several tables, you might be able to create a function to
> create the views. The function would take a table name and the desired
> prefix as parameters and programatically construct, then EXECUTE the
> CREATE VIEW statement.
>
> --Lee
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
Lee, I hadn't thought of this, and it's a great solution if I was
always doing the self-joins on the same table each time because it
plays out faster once the view-setup is out of the way. I'd only have
to reference the correct view for each part of the join. I didn't
specify, but I am looking for something more dynamic than this. To
use this effectively, I'd have to create a number of these views for
every one of my tables, which just feels like too much clutter for
something that feels like it should be simpler.
> In times like these, I usually write a query using
> information_schema.columns to generate the column list:
>
>
> SELECT ordinal_position,
> 1 AS table_instance,
> 'a.' || column_name || ' AS ' || column_name || '_a,'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'your_table_here'
> UNION ALL
> SELECT ordinal_position,
> 2 AS table_instance,
> 'b.' || column_name || ' AS ' || column_name || '_b,'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'your_table_here'
> ORDER BY table_instance,
> ordinal_position;
>
>
> Or something along those lines, and copy-and-paste the results into the
> query. It's quicker than typing them all out once you hit a certain number
> of columns, and certainly less typo-prone.
>
> It's not the shortcut you were thinking of but it works.
Stephen, I think this'll do the trick very nicely since it fits my
dynamic needs. I'll probably end up turning it into a PGAdmin macro
in order to make it even easier on myself. I dug in a bit on the
INFORMATION_SCHEMA and found that INFORMATION_SCHEMA.attributes will
help me use this same logic to build column lists when I'm working
with stored functions in lieu of tables. At least it'll work on those
I have defined to return composite data types. I don't suppose
there's a way to do this with functions that define multiple OUT
parameters in lieu of a custom composite type?
Thanks everyone!
sg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: distinguishing identical columns after joins
@ 2011-03-02 14:24 S G <sgennaria2@gmail.com>
parent: S G <sgennaria2@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: S G @ 2011-03-02 14:24 UTC (permalink / raw)
To: pgsql-sql
> I don't suppose
> there's a way to do this with functions that define multiple OUT
> parameters in lieu of a custom composite type?
Just had to dig a little further. Found my own answer in
INFORMATION_SCHEMA.PARAMETERS. Though I wish the specific_name column
didn't have that integer tagged at the end of each of my function
names. I'll find a way to work around it.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-03-02 14:24 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-01 19:47 distinguishing identical columns after joins S G <sgennaria2@gmail.com>
2011-03-01 19:53 ` Rob Sargent <robjsargent@gmail.com>
2011-03-01 22:13 ` S G <sgennaria2@gmail.com>
2011-03-01 23:00 ` Rob Sargent <robjsargent@gmail.com>
2011-03-01 23:44 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2011-03-02 13:45 ` S G <sgennaria2@gmail.com>
2011-03-02 14:24 ` S G <sgennaria2@gmail.com>
2011-03-01 23:37 ` Stephen Cook <sclists@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox