PostgreSQL - upsert - creating a table with created at and updated at columns

Here’s how you can create a new table (or update an existing table) in PostgreSQL that has the right table structure for upsert (primary key and last modified column).

Let’s start by creating a basic table and inserting a record into it:

CREATE TABLE test.test_updated_at (
    id      SERIAL,
    comment VARCHAR
)
;

INSERT INTO
    test.test_updated_at (comment)
VALUES
    ('hello world - no created_at or updated_at')
;

Here’s the table:

id comment
1 hello world - no created_at or updated_at

We have a primary key id in the table that auto increments (data type SERIAL), but we don’t have a way to know when the table was created or more specifically updated which is necessary for upsert.

Let’s add a column named created_at that has the timestamp when the record was created:

ALTER TABLE test.test_updated_at
    ADD COLUMN created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
;

Here’s the table:

id comment created_at
1 hello world - no created_at or updated_at 2021-03-31 16:53:09.172260

If we insert another row into the table, it will automatically have a created_at value:

INSERT INTO
    test.test_updated_at (comment)
VALUES
    ('added created_at')
;

Here’s the table:

id comment created_at
1 hello world - no created_at or updated_at 2021-03-31 16:53:09.172260
2 added created_at 2021-03-31 16:53:36.688010

The created_at column doesn’t change when records are updated, however, so we can’t use this column as the last modified column for upsert.

So let’s add a new column named updated_at so we can upsert against this table.

This part requires a couple extra steps.

First let’s create the new column updated_at with a default value:

ALTER TABLE test.test_updated_at
    ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
;

Next let’s create a function to determine the timestamp of any changes to a record:

CREATE OR REPLACE FUNCTION set_updated_at()
    RETURNS TRIGGER AS
$$
BEGIN
    OLD.updated_at = NOW();
    RETURN OLD;
END;
$$ LANGUAGE 'plpgsql'
;

Finally, let’s add a trigger on our table that runs this function when a record is updated or inserted:

CREATE TRIGGER set_updated_at
    BEFORE UPDATE
    ON test.test_updated_at
    FOR EACH ROW
EXECUTE PROCEDURE set_updated_at()
;

Here’s the table:

id comment created_at updated_at
1 hello world - no created_at or updated_at 2021-03-31 16:53:09.172260 2021-03-31 16:58:00.074575
2 added created_at 2021-03-31 16:53:36.688010 2021-03-31 16:58:00.074575

Let’s insert a new record:

INSERT INTO
    test.test_updated_at (comment)
VALUES
    ('updated_at created')
;

Here’s the table:

id comment created_at updated_at
1 hello world - no created_at or updated_at 2021-03-31 16:53:09.172260 2021-03-31 16:58:00.074575
2 added created_at 2021-03-31 16:53:36.688010 2021-03-31 16:58:00.074575
3 updated_at created 2021-03-31 16:58:57.505128 2021-03-31 16:58:57.505128

And let’s update an existing row:

UPDATE test.test_updated_at
SET
    comment = 'hello world updated row'
WHERE
    id = 1
;

Here’s the final result:

id comment created_at updated_at
2 added created_at 2021-03-31 16:53:36.688010 2021-03-31 16:58:00.074575
3 updated_at created 2021-03-31 16:58:57.505128 2021-03-31 16:58:57.505128
1 hello world - no created_at or updated_at 2021-03-31 16:53:09.172260 2021-03-31 16:59:14.560762