PostgreSQL Timestamp – How to Set Default now() in Timestamp Array

arraydefault valuepostgresqltimestamp

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 testarray TIMESTAMP[] DEFAULT '{NOW()}';
ALTER TABLE
# ALTER TABLE messages ADD COLUMN testarray2 TIMESTAMP[] DEFAULT '{"NOW()"}';
ALTER TABLE


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[]

Best Answer

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;

http://sqlfiddle.com/#!17/c3a31/4

Related Question