Thread: SELECT my_table.varchar FROM my_table
Hello, I accidentally encountered a feature in Postgres 8.3 that I couldn't find in the documentation while submitting a query like SELECT my_table.varchar FROM my_table which returns a concatenated string of all field values per row. I wonder where this is documented (and if it has something to do with composite types). Can anyone please explain? Thanks, Jan
Attachment
On Mon, May 31, 2010 at 7:48 AM, Jan Strube <js@deriva.de> wrote: > I accidentally encountered a feature in Postgres 8.3 that I couldn't find in > the documentation while submitting a query like > > SELECT my_table.varchar FROM my_table > > which returns a concatenated string of all field values per row. > I wonder where this is documented (and if it has something to do with > composite types). > > Can anyone please explain? I don't really know, but the result looks more like a single field formatted as "ROW-WISE" rather than CSV. The official way to get this result is: SELECT ROW( my_table.* ) FROM my_table; http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard Broersma <richard.broersma@gmail.com> writes: > On Mon, May 31, 2010 at 7:48 AM, Jan Strube <js@deriva.de> wrote: >> I accidentally encountered a feature in Postgres 8.3 that I couldn't find in >> the documentation while submitting a query like >> >> SELECT my_table.varchar FROM my_table >> >> which returns a concatenated string of all field values per row. >> I wonder where this is documented (and if it has something to do with >> composite types). >> >> Can anyone please explain? > I don't really know, but the result looks more like a single field It's equivalent to (my_table.*)::varchar. We've seen enough people confused by this (or the equivalent cases with text and name as the target type) that I wonder if we should intentionally break the symmetry and disable treating this case as a cast. Although I do rather wonder what the OP expected to happen here. regards, tom lane
Am 31.05.2010 17:44, schrieb Tom Lane: > Richard Broersma<richard.broersma@gmail.com> writes: > >> On Mon, May 31, 2010 at 7:48 AM, Jan Strube<js@deriva.de> wrote: >> >>> I accidentally encountered a feature in Postgres 8.3 that I couldn't find in >>> the documentation while submitting a query like >>> >>> SELECT my_table.varchar FROM my_table >>> >>> which returns a concatenated string of all field values per row. >>> I wonder where this is documented (and if it has something to do with >>> composite types). >>> >>> Can anyone please explain? >>> > >> I don't really know, but the result looks more like a single field >> > It's equivalent to (my_table.*)::varchar. We've seen enough people > confused by this (or the equivalent cases with text and name as > the target type) that I wonder if we should intentionally break the > symmetry and disable treating this case as a cast. Although I do > rather wonder what the OP expected to happen here. > I didn't expect anything special, because my original statement was actually a typo. So I was just amazed that I didn't get an error. Thanks for the explanation, Jan