Partial updates with functions

Question

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?

Answer 1

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

• woop: I'm building an API that offers PATCH HTTP methods to update a selection of data fields. With your approach I have to first get the missing fields not provided by the user and only then call the update function. In my mind this is inefficient by requiring unnecessary work.
• Evan Carroll: @woop No you don't.. You just need to dynamically create SQL from the patch. If the user sends only bar then blindly create the SQL to update bar if bar is different. If the user sends bar and baz, then blindly create the SQL to update if bar and baz are different. The only thing you need to do is check for the primary key and dynamically generate the rest.