CODEWITHSUNDEEP
sqlpostgresqltimestamptimescaledb
Hoon
Hoon

Reputation: 397

Data move from table to table , column type error

I'm using Postgresql with timescaledb extension, pg13 - tsdb 2.2.1

table 1

create table user 
(
    ...
    join_dt timestamptz NULL,
    ...
);

Table 2

create table a_user
( 
    ...
    join_dt timestamptz NULL,
    ...
);

I used SQL insert to a_user table so there's data inside the table, and want to move the data to user table by querying this :

insert into user 
    select * from a_user;

I get this error:

[42804] ERROR: column "join_dt" is of type timestamp with time zone but expression is of type character varying

The original data comes from a table which is like this

create table ori_user
(
    ...
    join_dt timestamp(6) with time zone,
    ...
);

I export data as SQL insert form from ori_user, and then inserted into a_user, and now I want to move from a_user to user.

This is what I have tried:

insert into user select 
...
join_dt::timestamptz,
...
from a_user;

does not work.

with aa as (select  pg_typeof(join_dt)::varchar as type,*  from a_user)
select * from aa where aa.type not like 'timestamp with time zone';

shows no row.

any other solutions? please help. Thanks in advance.

Upvotes: 0

Views: 140

Answers (1)

Hoon
Hoon

Reputation: 397

I thought table columns are in same order, but it wasn't.

fix the order, problem solved.

Upvotes: 1

Related Questions