I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've tried these two things and they both result in now() being executed at the time of the statement. Is it possible for me to achieve this with a simple default value, or do I need to write a hook?

# ALTER TABLE messages ADD COLUMN testarray2 TIMESTAMP[] DEFAULT '{"NOW()"}';

freedom_development=# \d messages
                                                 Table "public.messages"
    Column     |             Type              |                                Modifiers                                
 id            | integer                       | not null default nextval('messages_id_seq'::regclass)
 testarray     | timestamp without time zone[] | default '{"2017-12-30 21:00:09.622951"}'::timestamp without time zone[]
 testarray2    | timestamp without time zone[] | default '{"2017-12-30 21:00:19.936001"}'::timestamp without time zone[]
  • never use time stamp without time zone. Jan 1, 2018 at 21:05
  • @EvanCarroll why? Jan 2, 2018 at 7:04
  • Because you're just turning off functionality that is going to help you, why would you want to assume every timestamp is already in UTC? The real world doesn't work that way. You want to convert to UTC and store as UTC. That's what timestamp WITH time zone does. Jan 2, 2018 at 16:44
  • @EvanCarroll in all my applications in my entire medium-length career i've only ever wanted timestamps in utc, and i've always ensured that's how they are generated and stored. postgres or linux being configured to have a non-utc timezone would be considered a big error to me. and it's more efficient to store and work with no-timzone timestamps, no? Jan 2, 2018 at 21:01
  • 1
    @JohnBachir No, TIMESTAMP both with and without time zone are stored exactly the same on Postgres. The difference is that WITH means "with respect for zone/offset", where any offset or zone info passed with input is used to adjust the date-time into UTC before storing. The WITHOUT type ignores any passed zone/offset info, so for example noon in Montréal and noon in Kolkata are both stored as noon rather than adjusted to UTC. Jan 3, 2018 at 7:39

1 Answer 1


You need to use the array[] syntax:

create table messages(
    id serial primary key,
    val text not null,
    testarray timestamp[] not null default array[now()]

insert into messages(val) values ('blah');

select * from messages;


  • 1
    FWIW CURRENT_TIMESTAMP is the “standard” way to spell this, although \d messages will show postgresql stores it internally as now() :o) Dec 31, 2017 at 5:19
  • @WillCrawford yeah i discovered as much in my reading the docs and trying it out. postres is a bit annoying about now(), having it do what makes sense in context in some cases, but then mean something specific in other cases. Jan 2, 2018 at 6:54

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.