Postgresql – Partial updates with functions

functionspostgresqlupdate

My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided.

Let's take a posts table as an example:

+-----------------------------------+
|             post table            |
+-----------------------------------+
| post_id | SERIAL PRIMARY KEY      |
| title   | VARCHAR(100)            |
| content | TEXT                    |
+-----------------------------------+

The application can update the data when one or any combination of fields change. For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

This is a simple example, but it applies to all tables with many more columns, etc.

There are some ways I can think of:

  1. I could make a function for every column: post_update_title(changed_title, post_id), post_update_content(changed_content, post_id). Each of them only change the specific column they are responsible for. But this approach gets messy really quickly when there are tables with many columns.
  2. Create a general function that takes in the column name, changed value and post ID: post_update_column(column_name, changed_value, post_id). This approach makes me share information specific to the database with my applications. This is something I don't want.
  3. Create a general function for updating and set a default NULL value to parameters. This way I could detect which columns have to be changed. But this introduces a problem when there are columns that can be set NULL themselves. Then I couldn't say if the column was not updated or was explicitly set NULL.
  4. Call UPDATE queries directly from inside the application. This approach I dislike the most: I don't want to couple the application with the DB this way.

Has anyone faced something similar? How did you solve it?

Best Answer

For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

Why? When either of them changes just update both of them. It's an MVCC database and any change to the table will result in a whole new row being written and the old one being marked as dead.

UPDATE post
SET title = $2, content = $3
WHERE post_id = $1
  AND title <> $2
  OR content <> $3;

And don't use VARCHAR(100) use text

Related Question