Search Postgresql Archives

Re: SELECT ROW(t.*) FROM table t and nulls with textual representation?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Dec 8, 2009 at 5:43 AM, Teemu Juntunen
<teemu.juntunen@xxxxxxxxxx> wrote:
> Hello,
>
> is there any possibility to get null-values as text 'null' from
> dynamic select expression like
>
> SELECT ROW(t.*) FROM table t
>
> Normally you get
>
> (t1,t2,t3,,t5,,,)
>
> How can you get
>
> (t1,t2,t3,null,t5,null,null,null)
>
> I would like to use the result with dynamic update expression and update
> doesn't like of empty values, so
>
> UPDATE SET (t1,t2,t3,t4,t5,t6,t7,t8) = (t1,t2,t3,,t5,,,)

It can't be done.  The problem is that the update statement has a
funky syntax which is not composite type friendly.  What I would
personally like to be table to do is:
update foo set foo = (x,y,z)::foo where...
then you could do:
update foo set foo = '(a,b,)'::foo where...

Couple of posssible workarounds:
*) If you are willing to put the fields you are updating as a block
into a composite type, then you could update them as a block:
create table bar as (...)
create table foo (b bar, ...)
update foo set bar = ()::bar where...;

This isn't a general solution obviously.

*) convert your update to insert+delete:
delete from foo where...
insert into foo select '(a,b,)'::foo;

watch that race condition!

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux