Optional column update if provided value for column is not null

Question

I have following table:

CREATE TABLE IF NOT EXISTS categories
(
    id SERIAL PRIMARY KEY,
    title CHARACTER VARYING(100) NOT NULL,
    description CHARACTER VARYING(200) NULL,
    category_type CHARACTER VARYING(100) NOT NULL
);

I am using pg-promise, and I want to provide optional update of columns:

categories.update = function (categoryTitle, toUpdateCategory) {
  return this.db.oneOrNone(sql.update, [
          categoryTitle,
          toUpdateCategory.title, toUpdateCategory.category_type, toUpdateCategory.description,
        ])
}

• categoryName - is required
• toUpdateCategory.title - is required
• toUpdateCategory.category_type - is optional (can be passed or undefined)
• toUpdateCategory.description - is optional (can be passed or undefined)

I want to build UPDATE query for updating only provided columns:

UPDATE categories
SET title=$2,
// ... SET category_type=$3 if $3 is no NULL otherwise keep old category_type value
// ... SET description=$4 if $4 is no NULL otherwise keep old description value
WHERE title = $1
RETURNING *;

How can I achieve this optional column update in Postgres?

Answer 1

You could coalesce between the old and the new values:

UPDATE categories
SET title=$2,
    category_type = COALESCE($3, category_type),
    description = COALESCE($4, description) -- etc...
WHERE title = $1