Search code examples
postgresqljsonb

Postgresql jsonb default insert value with dynamic timestamp

I have a status column in my postgres database with the type jsonb

I can prepopulate the json with something like this.

CREATE TABLE ... status jsonb NOT NULL DEFAULT '{"hello": "world"}' ...

But what I am asking and what I would like to have is a JSON property with a timestamp prepolulated using the function now()

CREATE TABLE ... status jsonb NOT NULL DEFAULT '{"created_at": "'now()'"}' ... CREATE TABLE ... status jsonb NOT NULL DEFAULT '{"created_at": "'+now()'+"}' ...

Unfortunately these approaches above do not work. What would be the way to do it?

Solution

  • You must create the JSONB at runtime using jsonb_build_object:

    CREATE TABLE foobar 
    (status jsonb NOT NULL DEFAULT jsonb_build_object('created_at', now()));

    SQL Fiddle here.