4

Take a table foo,

CREATE TABLE foo(a,b,c,d)
AS VALUES
  (1,2,3,4);

I can insert into foo with a row type..

INSERT INTO foo
  SELECT (foo).*
  FROM foo;

INSERT INTO foo
  SELECT (v).*
  FROM ( VALUES (42,42,42,42) )
    AS v;

But I can't update foo with a row type.

-- fails
UPDATE foo SET (a,b,c,d) = (
  SELECT ROW(1,2,3,4)
).*;

-- also fails.
UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));
ERROR:  number of columns does not match number of values
LINE 1: UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));

Is there any syntax to update an entire row with a composite type. The really weird thing is that this works in a trigger with NEW, and I'm trying to write the same code without a trigger.

CREATE OR REPLACE FUNCTION foo()
RETURNS trigger
AS $$
  BEGIN
    NEW=(42,42,42,42);
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql
IMMUTABLE;

CREATE TRIGGER ok
  BEFORE UPDATE ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE foo();

-- The NEW row in trigger gets updated with (42,42,42,42)
-- the UPDATE succeeds; all rows (42,42,42,42)
UPDATE foo
  SET (a,b,c,d) = (1,2,3,4);

You can see here that we can update the row object inside the procedure, and change the update. However, we can't do that in the UPDATE ddl? I would expect the RECORD produced by the same name of the table to work the same way.

-- Such as this,
UPDATE foo
  SET foo = (1,2,3,4);
  • The trigger works, ok. I meant there is no code there that has update .. set (list_of_columns) = row(..); – ypercubeᵀᴹ Sep 15 '17 at 15:39
  • When you update NEW, you're updating a record (per the docs). That's what I want to do. I want to update a record with UPDATE -- the record that represents the current row typed to the table, that has the same name. I just don't know if I'm not getting the syntax right or if it's impossible. – Evan Carroll Sep 15 '17 at 15:46
  • Yes, I understood what you want. I think it's not possible, yet. There was a discussion at pgsql-hackers list. See mail-archive.com/pgsql-hackers@postgresql.org/msg298265.html – ypercubeᵀᴹ Sep 15 '17 at 16:09
  • Yes, no response to that patch -- guess it never got committed? =( Here is another one mail-archive.com/pgsql-hackers@postgresql.org/msg237578.html If you want to make an answer with those links I'll upvote and mark as accepted. Seems the answer is "it's not possible, yet" – Evan Carroll Sep 15 '17 at 16:31
1

There are actually lots of posts regarding this..

However, according to /u/pstef, one of these is committed, and this syntax now works on HEAD, though @ypercubeᵀᴹ reports it does not work on 9.6.5

UPDATE foo SET (a,b,c,d) = ROW(10,9,8,7);

The docs don't say much about this

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. **PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. **An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.

But you can also see at the syntax for UPDATE

   ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
  • Regarding link 2: "It works for me on HEAD" I suppose that means it still works only in beta. I don't see it working in 9.6.5. – ypercubeᵀᴹ Sep 15 '17 at 17:38
  • @ypercubeᵀᴹ good note, updated. I don't have a running 9.6. – Evan Carroll Sep 15 '17 at 17:44
0

How about (server 9.5.7) ...

create table foo(a,b,c,d)
as values
  (1,2,3,4)
, (4,5,6,7)
, (8,9,0,null);

postgres=# select * from foo;
 a | b | c | d 
---+---+---+---
 1 | 2 | 3 | 4
 4 | 5 | 6 | 7
 8 | 9 | 0 |  
(3 rows)


update foo
set (a,b,c,d) = ( 
  select (v).*
  from ( values (42,42,42,42) ) as v 
) ;

postgres=# select * from foo;
 a  | b  | c  | d  
----+----+----+----
 42 | 42 | 42 | 42
 42 | 42 | 42 | 42
 42 | 42 | 42 | 42
(3 rows)

Dbfiddle here.

Or, indeed (without trigger):

postgres=# update foo
postgres-# set (a,b,c,d) = (11,22,33,44) ;
UPDATE 3
postgres=# select * from foo;
 a  | b  | c  | d  
----+----+----+----
 11 | 22 | 33 | 44
 11 | 22 | 33 | 44
 11 | 22 | 33 | 44
(3 rows)
  • I can see how this looks really close to what I'm looking but it's not at all it. I almost want to up vote for the attempt. Row-objects can be converted to and from other types. For instance, SELECT new.* FROM foo CROSS JOIN LATERAL jsonb_populate_record( null::foo, to_jsonb(foo) || jsonb_build_object(CASE WHEN random() > 0.5 THEN 'a' ELSE 'b' END, 7) || jsonb_build_object(CASE WHEN random() > 0.5 THEN 'c' ELSE 'd' END, 13) ) AS new; That's complex, but that's what I'm trying to do with the row objects. Now try to update the original row with it, and do it all inline. – Evan Carroll Sep 15 '17 at 19:12
  • When using your SELECT to create a new table from "foo", the UPDATEs work the same as before. See dbfiddle.uk/… – stefan Sep 16 '17 at 6:48
  • I know what you are on about, though. – stefan Sep 16 '17 at 7:01
0

This seems to work (in versions 9.5 and 9.6) but I'm not if it will solve your issue.

  • convert the row into type foo:

    row(1,2,3,4)::foo
    
  • expand it into columns:

    select ( ).*
    

The whole code:

update foo set (a,b,c,d) = (select (row(1,2,3,4)::foo).*) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.