Issue with timestamps stored in a PostgreSQL JSON column

[jbelien]

• Laravel Version: 9.17.0
• PHP Version: 8.1.6
• Database Driver & Version: pgsql

Description:

When using a JSON column in PostgreSQL for timestamps created_at, updated_at, and deleted_at, there is an issue with update and (soft) delete.

Error on update

SQLSTATE[42601]: Syntax error: 7 ERROR: multiple assignments to same column "metadata"

Query:

update "mytable" set "metadata" = '{"creator":"Jonathan","created_at":"2022-06-01 15:45:00+00","updated_at":"2022-06-21 13:31:14"}', ..., "metadata" = jsonb_set("metadata"::jsonb, '{"updated_at"}', '2022-06-21 13:31:14') where "id" = '0e518c37-9429-42a5-90a6-09dc339acc82'

Error on delete

Undefined array key "metadata->deleted_at"

Error is triggered by syncOriginalAttributes() function. https://github.com/laravel/framework/blob/ae6098381ad5cd4eb44394f0b2395c855c086684/src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php#L1809-L1820

Steps To Reproduce:

1. Create a PostgreSQL table with a JSON column to store your timestamps (call it metadata, for instance)

2. Create a model using that metadata column for your timestamps and enable soft delete

   class MyClass extends Model
   {
       use SoftDeletes;
   
       public const CREATED_AT = 'metadata->created_at';
       public const UPDATED_AT = 'metadata->updated_at';
       public const DELETED_AT = 'metadata->deleted_at';
   
       ...
   }

3. Create a new record and save it in the database ; the metadata column will be correctly filled with {"created_at": ...}

4. Try to update it (first error)

5. Try to (soft) delete it (second error)

[driesvints]

I don't think we support JSON columns for timestamps at this time.

[jbelien]

That's a bit unfortunate because most of it works just fine ... but so be it.