Lists: | pgsql-general |
---|
From: | Jan Strube <js(at)deriva(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SELECT my_table.varchar FROM my_table |
Date: | 2010-05-31 14:48:08 |
Message-ID: | 4C03CC28.5080405@deriva.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | Jan Strube <js(at)deriva(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT my_table.varchar FROM my_table |
Date: | 2010-05-31 15:26:31 |
Message-ID: | AANLkTimzdbG85IwDQtQhRKcNcsv2QxsVJu12fBiJUoub@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, May 31, 2010 at 7:48 AM, Jan Strube <js(at)deriva(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | Jan Strube <js(at)deriva(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT my_table.varchar FROM my_table |
Date: | 2010-05-31 15:44:42 |
Message-ID: | 13743.1275320682@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Richard Broersma <richard(dot)broersma(at)gmail(dot)com> writes:
> On Mon, May 31, 2010 at 7:48 AM, Jan Strube <js(at)deriva(dot)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
From: | Jan Strube <js(at)deriva(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT my_table.varchar FROM my_table |
Date: | 2010-05-31 15:59:15 |
Message-ID: | 4C03DCD3.5050104@deriva.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Am 31.05.2010 17:44, schrieb Tom Lane:
> Richard Broersma<richard(dot)broersma(at)gmail(dot)com> writes:
>
>> On Mon, May 31, 2010 at 7:48 AM, Jan Strube<js(at)deriva(dot)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