From: S G <sgennaria2@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: distinguishing identical columns after joins
Date: Wed, 2 Mar 2011 08:45:59 -0500
Message-ID: <AANLkTineGPTvF2-abv8AJcrcaGEm0xJDKgEsED8-eoRz@mail.gmail.com> (raw)
In-Reply-To: <4D6D84CE.9010200@gmail.com>
References: <AANLkTinSR8Wuago7HdATetEiXQLU8fjbnm2tX5n1RGHG@mail.gmail.com>
<4D6D4EC2.70306@gmail.com>
<AANLkTimzBrzzdKa4=k9VnXLVNzw2U_PzOSbRzhCZJhn=@mail.gmail.com>
<4D6D7A97.7050704@gmail.com>
<4D6D84CE.9010200@gmail.com>
>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgennaria2@gmail.com
Subject: Re: distinguishing identical columns after joins
In-Reply-To: <AANLkTineGPTvF2-abv8AJcrcaGEm0xJDKgEsED8-eoRz@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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