Is is possible to call a function, like now()
when inserting/updating a JSONB column in Postgres?
I was thinking something like the following where time
would be set to the return value of now()
:
SELECT '{"value": 5, "time": now()}'::JSONB;
functionsjsonpostgresql
Is is possible to call a function, like now()
when inserting/updating a JSONB column in Postgres?
I was thinking something like the following where time
would be set to the return value of now()
:
SELECT '{"value": 5, "time": now()}'::JSONB;
Best Answer
Sure, JSON doesn't support a special date type, (neither does JavaScript), JSONB only supports
string
number
boolean
null
So just use string interpolation, it'll get stored the same way:
If you're trying to simply get
NOW()
from the server in one call, you can certainly do that usingjsonb_set
, or jsonb concatenation (||
). Here are the docsIn a third party library this may look like..
It's still ugly. I wouldn't store a datatime in a JSONB, unless I had to:
As an aside, one of the alternatives to JSONB that Pg could have chosen is BSON which doesn't store dates as strings..