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 |