CODEWITHSUNDEEP
sqlpostgresqldatetimesql-insert
arilwan
arilwan

Reputation: 3993

Inserting into table1 from select table2

I have the following two tables:

postgresp=> \d tempo
                          Table "postgres.tempo"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+---------
 user_id    | integer                |           |          | 
 session_id | bigint                 |           |          | 
 lat        | double precision       |           |          | 
 lon        | double precision       |           |          | 
 flag       | integer                |           |          | 
 alt        | double precision       |           |          | 
 days       | double precision       |           |          | 
 gpsdate    | date                   |           |          | 
 gpstime    | time without time zone |           |          |

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

Then I want to insert records from tempo into trajectory by:

  1. add 1 to user_id
  2. concaante gpsdate and gpstime fields from tempo into timestamp field in trajectory.

Query:

INSERT INTO trajectory (user_id, session_id, timestamp, lat, lon, alt)
SELECT user_id + 1
    , session_id

    , CONCAT(gpsdate, ' ', gpstime)
    , lat
    , lon
    , alt
FROM tempo

Error:

ERROR:  column "timestamp" is of type timestamp with time zone but expression is of type text
LINE 4:  , CONCAT (gpsdate, ' ', gpstime)
           ^
HINT:  You will need to rewrite or cast the expression.

Data in the tempo table:

posgres=> SELECT * FROM tempo LIMIT 10;
 user_id |   session_id   |    lat    |    lon     | flag | alt |       days       |  gpsdate   | gpstime  
---------+----------------+-----------+------------+------+-----+------------------+------------+----------
       0 | 20090429005954 | 40.008048 | 116.316474 |    0 | 491 | 39932.0415972222 | 2009-04-29 | 00:59:54
       0 | 20090429005954 | 40.009643 | 116.317842 |    0 | 300 | 39932.0416550926 | 2009-04-29 | 00:59:59
       0 | 20090429005954 | 40.009794 | 116.318053 |    0 | 283 |  39932.041712963 | 2009-04-29 | 01:00:04
       0 | 20090429005954 | 40.009646 |  116.31813 |    0 | 276 | 39932.0417708333 | 2009-04-29 | 01:00:09
       0 | 20090429005954 | 40.009554 | 116.318121 |    0 | 273 | 39932.0418287037 | 2009-04-29 | 01:00:14
       0 | 20090429005954 | 40.009432 | 116.318115 |    0 | 269 | 39932.0418865741 | 2009-04-29 | 01:00:19
       0 | 20090429005954 |   40.0093 | 116.318127 |    0 | 266 | 39932.0419444444 | 2009-04-29 | 01:00:24
       0 | 20090429005954 | 40.009155 | 116.318091 |    0 | 265 | 39932.0420023148 | 2009-04-29 | 01:00:29
       0 | 20090429005954 | 40.009011 | 116.318092 |    0 | 265 | 39932.0420601852 | 2009-04-29 | 01:00:34
       0 | 20090429005954 | 40.008857 |  116.31812 |    0 | 261 | 39932.0421180556 | 2009-04-29 | 01:00:39
(10 rows)

EDIT

Using , CONCAT(gpsdate | | gpstime) also generates the following error:

ERROR:  operator does not exist: | time without time zone
LINE 4:  , CONCAT(gpsdate | | gpstime)
                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 0

Views: 84

Answers (1)

GMB
GMB

Reputation: 222582

As I understand your question, you want to generate a timestamp with time zone from a date and a time. You can do this with the + operator, and additional casting:

INSERT INTO trajectory (user_id, session_id, timestamp, lat, lon, alt)
SELECT 
    user_id + 1,
    session_id,
    (gpsdate + gpstime)::timestamp with time zone,
    lat,
    lon,
    alt
FROM tempo

Upvotes: 1

Related Questions