PostgreSQL: manipulating the type of a PRIMARY KEY column

Question

Here's the structure of my table:

 \d trajectories
                       Table "postgres.trajectories"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 user_id    | integer                  |           |          | 
 session_id | bigint                   |           | not null | 
 timestamp  | timestamp with time zone |           | not null | 
 lat        | double precision         |           | not null | 
 lon        | double precision         |           | not null | 
 alt        | double precision         |           |          | 
Indexes:
    "trajectories_pkey" PRIMARY KEY, btree (session_id, "timestamp")
    "trajec_idx" btree (user_id, "timestamp")

Sample data:

SELECT * FROM trajectories LIMIT 5;
user_id |    session_id     |       timestamp        |    lat    |    lon     | alt 
---------+-------------------+------------------------+-----------+------------+-----
      85 | 84020081204232933 | 2008-12-05 07:27:03+00 | 39.934484 | 116.430599 | 200
      85 | 84020081204232933 | 2008-12-05 07:27:08+00 | 39.934486 | 116.430635 | 199
      85 | 84020081204232933 | 2008-12-05 07:27:13+00 | 39.934493 | 116.430689 | 199
      85 | 84020081204232933 | 2008-12-05 07:27:18+00 | 39.934468 | 116.430648 | 199
      85 | 84020081204232933 | 2008-12-05 07:27:23+00 | 39.934467 | 116.430614 | 199
(5 rows)

I would like to convert the timestamp column to epoch and no longer in this datetime format.

Answer 1

That is a bad idea. It is much better to have timestamps in the database.

Anyway, here goes:

ALTER TABLE trajectories
   ALTER timestamp TYPE double precision USING extract(epoch FROM timestamp);