Postgres CASE Statement in an insert

Question

I have a table that i had to ALTER in Postgres. I would now like to insert some values for the rows that exists in the table based on a condition. I'm looking at using CASE blocks!

Here is what I have:

INSERT INTO MyTable (value1, value2) values
    (1, SELECT t.name,
          CASE WHEN t.name IN ('MyName') THEN 1
          ELSE 2
        END AS value2
       FROM MyTable t);

I get an error:

ERROR: syntax error at or near "select"
SQL state: 42601
Character: 71

Any clues what it is?

Solution

OK, this is the insert query with the syntax fixed

 INSERT INTO MyTable (value1, value2) 
        SELECT t.name,
              CASE WHEN t.name IN ('MyName') THEN 1
              ELSE 2
            END AS value2
           FROM MyTable;

If you're trying to change existing rows, you need an update query, e.g.

-- first update, set value1 to 1 and value2 for all rows
UPDATE MyTable set value1 = 1,value2 = 2;

-- next query. Set value2 = 1 for only those rows matching your criteria
 UPDATE MyTable
    SET value2 = 1 WHERE name IN ('MyName');

Comments

[mlinth]

Couple of things: if you're inserting using a select, you don't need the "values" statement. You also appear to have two columns in your table, and are trying to insert three values - 1, t.name and your case statement. Do you want just to insert t.name and the case statement?

[joesan]

I want to check what the name is and based on that I want to insert either 1 or 2. It is only the two columns that I want to insert

[joesan]

Essentially, I'm trying to update my table with values for these two columns

[mlinth]

Sure you don't want an UPDATE query, in that case?

[joesan]

Yes, I guess an UPDATE query would fit in nicely

[joesan]

What I want is that the case condition is applied only for value2. For value1, I have a default value that I would like to insert

[mlinth]

I'd use two updates then (or even set a default value for your column). First update sets your default (update mytable set value1 = 1), the second only matching your criteria.