[SQL] 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 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinguishing identical columns after joins
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
Re: [SQL] Problem with serial counters
On 24/02/11 21:09, Jacques Lebrun wrote: I guess when I do an insert with all the fields (including the RowId), Postgres does not increment the serial counter. I cannot remove the RowId field from the Insert of my setup script because this setup script is also used by customers using MySQL. Apart from Vibhor's suggestion (which is the typical way PG does it) you can also set the auto field to DEFAULT: INSERT INTO mytable (rowid, other) VALUES (DEFAULT,'a'), (DEFAULT,'b'); This should work on either system. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinguishing identical columns after joins
On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinguishing identical columns after joins
On 03/01/2011 03:13 PM, S G wrote: > On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent 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?:) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinguishing identical columns after joins
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,' FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_here' UNION ALL SELECT ordinal_position, 2 AS table_instance, 'b.' || column_name || ' AS ' || column_name || '_b,' FROMINFORMATION_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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] distinguishing identical columns after joins
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 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
