Thread: distinguishing identical columns after joins
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 anyway 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
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?
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
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?:)
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.
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
>> 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
> 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.