PostgreSQL how to set a Primary Key column with timestamp to automatically be filled

Question

How could I achieve this in PostgreSQL??

I want to have a table with a timestamp field that automatically gets the value of now() on insert. The timestamp will never be updated. I assume I need to create a trigger to do this. If there is an easier way, please advise.

Perhaps, make the column so it has a default value of now, land then insert without specifying the timestamp column so it gets the default value?

create table foo (q_i_time timestamp with time zone not null default now(), someval int);

CREATE TABLE
billing=# insert into foo (someval) values (22);
INSERT 0 1
billing=# insert into foo (someval) values (26);
INSERT 0 1
billing=# insert into foo (someval) values (1);
INSERT 0 1
billing=# select * from foo;

           q_i_time            | someval
-------------------------------+---------
 2008-02-25 17:23:03.247619-08 |      22
 2008-02-25 17:23:07.43922-08  |      26
 2008-02-25 17:23:10.111189-08 |       1
(3 rows)

Can this autofilled timestamp column be the primary key?

Thanks Advanced.

Answer 1

There is no need for a trigger, since you're not updating the timestamp. Simply declare a DEFAULT, e.g.

CREATE TABLE demo (
    id integer primary key,
    created_at timestamp not null default current_timestamp
);

example:

test=>     CREATE TABLE demo (
test(>         id integer primary key,
test(>         created_at timestamp not null default current_timestamp
test(>     );
CREATE TABLE
test=> INSERT INTO demo(id) VALUES(1),(2);
INSERT 0 2
test=> INSERT INTO demo(id) VALUES(3);
INSERT 0 1
test=> SELECT * FROM demo;
 id |         created_at         
----+----------------------------
  1 | 2015-06-13 12:30:55.169221
  2 | 2015-06-13 12:30:55.169221
  3 | 2015-06-13 12:30:57.395104
(3 rows)

Note that the first two timestamps are the same since it was a single transaction. If you don't want that, use clock_timestamp instead of current_timestamp.

Note that applications can still override the timestamp by specifying it explicitly:

test=> INSERT INTO demo(id, created_at) VALUES (4, '2014-01-01 00:00:00');
INSERT 0 1
test=> SELECT * FROM demo WHERE id = 4;
 id |     created_at      
----+---------------------
  4 | 2014-01-01 00:00:00
(1 row)

You only need a trigger if you wish to force the value, or if you want to also update it when the row is updated.