Correcting wrong timezones/timestamps in existing tables

I did a huge mistake because I didn’t know how the TIMESTAMPTZ datatype works in postgresql.

We need to store sensor data from a handful of different sources.

We have a few tables with two timestamp columns, defined as follows.

CREATE TABLE discriminator0 (
    ...
    timestamp TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT (NOW() AT TIME ZONE 'CEST')
)

The sensors send their data without a timezone (it’s cest). In order to match the created_at column with the timestamp column, I set its default value to the current time at the cest timezone.

It didn’t occur to me that this is severely flawed until I tried to query the database from a client.

SELECT created_at, timestamp, tempoutside FROM d0 WHERE timestamp > '2020-07-20T13:56:00+02:00' LIMIT 9;

          created_at           |         timestamp          |  tempoutside
-------------------------------+----------------------------+---------------
 2020-07-20 11:56:07.591044+00 | 2020-07-20 11:56:00.001+00 |     27.734375
 2020-07-20 11:56:04.386565+00 | 2020-07-20 11:56:00.008+00 | 26.8798828125
 2020-07-20 11:56:08.562664+00 | 2020-07-20 11:56:00.012+00 | 27.3681640625
 2020-07-20 11:56:09.980161+00 | 2020-07-20 11:56:00.015+00 |  30.126953125
 2020-07-20 11:56:08.71351+00  | 2020-07-20 11:56:00.027+00 | 26.0986328125
 2020-07-20 11:56:06.031052+00 | 2020-07-20 11:56:00.03+00  |            25
 2020-07-20 11:56:05.752615+00 | 2020-07-20 11:56:00.033+00 |  27.001953125
 2020-07-20 11:56:07.248163+00 | 2020-07-20 11:56:00.033+00 | 27.6123046875
 2020-07-20 11:55:56.214678+00 | 2020-07-20 11:56:00.034+00 | 27.1728515625

The problem is, now that I’m specifying a timezone in the query, the result gets shifted by two hours because I stored a cest timestamp as a utc timestamp.

1. Can I define the timestamp column to be treated as cest even though the timestamp inserted doesn’t contain a time zone? I can’t modify the timestamp before inserting it into the database. Also, I would like to avoid setting the servers time zone, if possible, but i guess it isn’t.

I could write a trigger that locks the table, stores the id of the last inserted row somewhere, sets the server tz to cest and changes the default constraint.

Then I could run a simple update that fixes all prior timestamps. Is there anything that could go wrong with that?