CODEWITHSUNDEEP
postgresql
RotatingPieces
RotatingPieces

Reputation: 413

Update values of a row to the max of old and new value

In SQLite I am doing something like

UPDATE some_table SET
    some_string = "hund",
    score = MAX(score, 42)
    hair_density = hair_density + 1
WHERE some_criteria = 31337;

Is there a similar feature in PostgreSQL?

From my reading of the docs and my experiments MAX() is only available in select statements. Like:

SELECT MAX(score) from some_table;

Is the an equivalent in PostgreSQL to the way I use MAX() in SQLite?

Upvotes: 0

Views: 767

Answers (1)

user330315
user330315

Reputation:

If that max() usage is used to get the bigger of the two numbers, the equivalent is greatest() in Postgres

UPDATE some_table 
  SET some_string = 'hund',
      score = greatest(score, 42)
      hair_density = hair_density + 1
WHERE some_criteria = 31337;

Additionally: string (varchar) values have to be enclosed with single quotes '- double quotes are only for identifiers (e.g. column or table names). So "hund" refers to a column, but 'hund' is a string constant.

Upvotes: 2

Related Questions