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;

  • If one of the answers works for you, can you mark it as accepted? Jan 10, 2017 at 5:10

2 Answers 2


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:

  '{"value": 5, "time": "' || now() || '" }'

If you're trying to simply get NOW() from the server in one call, you can certainly do that using jsonb_set, or jsonb concatenation (||). Here are the docs

SELECT '{"value": 5}'::JSONB || jsonb_build_object('time', now());

In a third party library this may look like..

INSERT INTO table (json)
VALUES ( ? || jsonb_build_object('time', now()) );

It's still ugly. I wouldn't store a datatime in a JSONB, unless I had to:

  • It will be slow.
  • It will take more space.

As an aside, one of the alternatives to JSONB that Pg could have chosen is BSON which doesn't store dates as strings..

  • That works, but is there a way to pull something like that off when using a library like node-postgres? Dec 8, 2016 at 23:52
  • @DaveJohansen updated. Dec 9, 2016 at 0:10

Just because you mentioned node-postgres now() "as json value" does not need jsonb_build_object if you pass it as prepared statement, Eg:

var pg = require('pg');
var conString = 'postgres://n@';
var pgClient = new pg.Client(conString);

var query = 'select concat(\'{"value": 5, "time": "\',$2::timestamptz,\'"}\')::jsonb, $1::timestamptz';
var values = ['now()','now()'];
var cb = function(err, result) {

    name: 'get some',
    text: query,
    values: values
  , cb


    concat: {
      time: '2016-12-09 14:16:28.539636+00',
      value: 5
    timestamptz: FriDec09201614: 16: 28GMT+0000(GMTStandardTime)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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