Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax.

I have an updated set of data in this form currently:

INSERT INTO bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null);

and I want to update the rows that are there already to the new data, I'm looking for something like the following where I can update all rows without repeating myself:

UPDATE ON ID
bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) 
VALUES(
        (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
        (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
);

The above would check for matching ID values, and update the matches with the new data.

Note: I am really looking to avoid manually mapping all the column names. PostgreSQL already knows my columns, and I've already mapped them on the data. Why do that again in a longer format?

share|improve this question
    
    
i'm after an update not upsert. –  Jharwood Jul 3 '13 at 10:16
add comment

3 Answers 3

up vote 5 down vote accepted

If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as 
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id, 
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;
share|improve this answer
    
The INSERT was showing the data format I was given to update with. –  Jharwood Jul 3 '13 at 13:03
    
This is closest to what i'm looking for, updated my question with a note regarding the mapping of columns. in the meantime this will work if not a bit more verbose when you have many columns –  Jharwood Jul 3 '13 at 13:10
    
@Jharwood: there is no way in Postgres (or SQL in general) to avoid the "column mapping" in an update statement. There is no "shorthand" notation. –  a_horse_with_no_name Jul 3 '13 at 13:15
    
+1: CTEs slipped my mind: that would probably have made my example cleaner too. I wish that extended "values" syntax were more common... –  David Spillett Jul 3 '13 at 13:29
1  
@Jharwood: as such a "shortcut" is not defined by the SQL standard, I doubt this will ever be implemented in Postgres. You can post such a request to the Postgres mailing list: postgresql.org/community/lists –  a_horse_with_no_name Jul 3 '13 at 14:17
show 3 more comments

IIRC postgres doesn't have MERGE or similar (though it does seem to be being talked about for later versions: http://wiki.postgresql.org/wiki/SQL_MERGE).

If you know all the rows in question exist you could do something like:

UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = 109
                 , PARENT_ID                       = 1
                 , BOUGHT_IN_FORM_TYPE_ID          = 28
                 , PRIORITY                        = 100
                 , NAME                            = 'Tooling'
                 , HEADING                         = 'Tooling'
                 , DESCRIPTION                     = 'Enter you Machine Tools Here'
                 , ICON                            = 'tooling.png'
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 1,0,1,200,'Bought In','Bought In','','boughtin.png',null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

adding an extra UNION for each subsequent row that needs updating, though that us rather long-winded for just a couple of updates and may not be terribly efficient (that set of UNIONs producing "newdata" will get thrown into a temporary table, potentially on disk, and there will be no index on ID in that temporary table which may be significant if there are many rows).

NOTE: The cast for BOUGHT_IN_CONTROL_PANEL_FILE_ID is to ensure the query planner knows the type (I've seen this cause trouble in MSSQL with it deciding early that a NULL should be an INT then finding a character type in that position in a later UNION clause - so I always specify a column type for literal NULL values when using UNION)

To make the first row the same format as the subsequent ones, to make programitically generating this sort of statement slightly easier, this hack will do the trick in MSSQL:

UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = CAST(NULL AS INT)
                 , PARENT_ID                       = CAST(NULL AS INT)
                 , BOUGHT_IN_FORM_TYPE_ID          = CAST(NULL AS INT)
                 , PRIORITY                        = CAST(NULL AS INT)
                 , NAME                            = CAST(NULL AS NVARCHAR(MAX))
                 , HEADING                         = CAST(NULL AS NVARCHAR(MAX))
                 , DESCRIPTION                     = CAST(NULL AS NVARCHAR(MAX))
                 , ICON                            = CAST(NULL AS NVARCHAR(MAX))
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 109, 1, 28, 100, 'Tooling'  , 'Tooling'  , 'Enter your Machine Tools here', 'tooling.png' , null
           UNION ALL SELECT 1  , 0, 1 , 200, 'Bought In', 'Bought In', ''                             , 'boughtin.png', null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

Caveat 1: this should be valid syntax for MSSQL, but I didn't test it and I don't know how standard all the syntax is so you might need tweaks for postgres (for instance not all query interpreters let you get away with not naming the columns after the first SELECT in a sequence of UNIONs).

Caveat 2: this feels somewhat hacky, and is considerably more verbose than the ideal in your example.

As an alternative, drop the new data into a temporary table and run two statements to update rows that do exist in the current data (then insert those that don't, if you are trying to UPSERT rather than just UPDATE), wrapping this in an explicit transaction to ensure that you get all-or-nothing consistency for the operation - that would likely be a lot cleaner despite not being a single statement.

share|improve this answer
    
The "dummy select" is not necessary in Postgres. You can use a values clause wherever you can use a table. –  a_horse_with_no_name Jul 3 '13 at 10:56
    
In which case my syntax above could probably be made a lot less long winded. It might still fell a tad hacky though. –  David Spillett Jul 3 '13 at 11:05
add comment

There is actually a shorter syntax variant for this, available since Postgres 8.2 (released 2006). Quoting the release notes:

  • Allow UPDATE to set multiple columns with a list of values (Susanne Ebrecht)

This is basically a short-hand for assigning the columns and values in pairs. The syntax is UPDATE tab SET (column, ...) = (val, ...).

Applied to your query:

UPDATE bought_in_control_panel b
SET   (  parent_id,   bought_in_form_type_id,   priority,   name,   heading,   description,   icon,   bought_in_control_panel_file_id) 
    = (v.parent_id, v.bought_in_form_type_id, v.priority, v.name, v.heading, v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

You still have to list all columns, but you can trim some noise and its easier to assemble a list, copy it and prepend the table alias of the source table. You can also do this dynamically. See below.

Also using a subquery, which works just as well as a CTE here, but with a bit less overhead (performance).

You can even mix lists and single columns freely, if you feel inclined to do so. No difference the result, but might come in handy for formatting or, more importantly, for dynamically generated commands:

UPDATE bought_in_control_panel b
SET    parent_id = v.parent_id
    , (  bought_in_form_type_id,   priority,   name,   heading) 
    = (v.bought_in_form_type_id, v.priority, v.name, v.heading) 
    , (  description,   icon,   bought_in_control_panel_file_id) 
    = (v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

It's all in the manual if you look closely.

Speaking of dynamic SQL, here is a related answer on SO that demonstrates how to fetch the list of columns from the system catalog pg_attribute and execute it all in a function:
Update multiple columns in a trigger function in plpgsql

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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