INSERT ON CONFLICT DO UPDATE returns unexpected values when used with RETURNING statement

I have a users table with id, name, created_at, and updated_at. Both the created_at, and updated_at have default values that default to the current timestamp. I am attempting to upsert into this table as follows.

INSERT INTO users (
    id,
    name,
    created_at,
    updated_at
) VALUES (
    '0e22e5b9-3826-401c-824b-ba12b1b4eb0a'
    'Some User',
     NOW(),
     NOW()
) ON CONFLICT (
    id
) DO UPDATE SET
    name = excluded."name",
    updated_at = excluded."updated_at"
RETURNING
    "id",
    "name",
    "created_at",
    "updated_at";

I want to make sure that when an insert happens, both the "created_at" and "updated_at" are set, but when there's a conflict and only an update happens, then only the "updated_at" is updated, and I want to return the final row. I expect that if I had an existing row like

and I updated the name two days later, I'd get back something like this from the return statement where only the updated_at has changed

But instead I get back results where both the created_at and updated_at have been changed

This problem is only with the returned value after the query runs, the actual value in the table is still correct. I assume this behavior has something to do with how the RETURNING command interacts with the upsert query, but I couldn't find documentation on this behavior anywhere. I know there are workarounds to this, but I'm just wondering why this specific method doesn't work. Is this a bug or expected behavior for PostreSQL?

The behavior you're experiencing is expected for PostgreSQL when using the RETURNING clause in combination with an INSERT ... ON CONFLICT ... DO UPDATE statement. This behavior is not specific to the RETURNING clause but rather how the ON CONFLICT ... DO UPDATE statement works.

When a conflict occurs and the DO UPDATE clause is triggered, all the columns specified in the SET clause will be updated, regardless of whether they were actually changed in the EXCLUDED row. This is the default behavior of the DO UPDATE clause.

In your case, both created_at and updated_at are specified in the SET clause, so even if created_at remains the same, it will still be updated with the value from excluded."created_at", which is the same as NOW() in your case.

To achieve the desired behavior, where created_at remains unchanged during an update, you can modify your query as follows:

INSERT INTO users (
    id,
    name,
    created_at,
    updated_at
) VALUES (
    '0e22e5b9-3826-401c-824b-ba12b1b4eb0a',
    'Some User',
     NOW(),
     NOW()
) ON CONFLICT (id) DO UPDATE SET
    name = excluded."name",
    updated_at = excluded."updated_at",
    created_at = users.created_at -- Keep the original value of created_at
RETURNING
    "id",
    "name",
    "created_at",
    "updated_at";

By explicitly setting created_at = users.created_at, you ensure that the created_at column retains its original value during an update and only the updated_at column gets updated.

This behavior is not a bug but rather a design choice in PostgreSQL's implementation of the ON CONFLICT ... DO UPDATE statement. It's designed to provide flexibility in updating columns during conflicts, even if the values are not changing.