1

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?

-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

  • 1
    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. – woop Dec 20 '17 at 23:22
  • @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. – Evan Carroll Dec 22 '17 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.