Column is of type timestamp without time zone but expression is of type character varying

Question

I am trying to move data from one table (in Redshift) to another table (in Redshift). The sql code uses INSERT INTO SELECT and somehow it's not working. The datatypes of columns in both the tables are different but in my SELECT clause, I am using SQL UDF to convert it into respective datatype.

For debugging purposes, I tried to upload that table with only one column (the column which is being errored out by Redhsift) in S3 using UPLOAD command and copied it into a table in Redshift using COPY command. Now when I tried to INSERT INTO SELECT, using this table which only has 1 column, using the UDF, it worked fine.

Here are the table definitions Destination table:

CREATE TABLE test.dn_ems_activity_kush
(
    tstamp_trans TIMESTAMP WITHOUT TIME ZONE ENCODE ZSTD
    ,tstamp_local VARCHAR(25) ENCODE RAW
    ,pan VARCHAR(131) ENCODE ZSTD
    ,retrieval_ref_no VARCHAR(12) ENCODE ZSTD
    ,case_type_ind VARCHAR(2) ENCODE ZSTD
    ,sys_trace_audit_no VARCHAR(6) ENCODE ZSTD
    ,case_no VARCHAR(14) ENCODE ZSTD
    ,net_id_ems VARCHAR(3) ENCODE ZSTD
    ,net_term_id VARCHAR(8) ENCODE ZSTD
    ,rpt_lvl_id_b VARCHAR(10) ENCODE ZSTD
    ,status VARCHAR(4) ENCODE ZSTD
    ,request_type VARCHAR(4) ENCODE ZSTD
    ,state_tstamp TIMESTAMP WITHOUT TIME ZONE ENCODE ZSTD
    ,state_expir_tstamp TIMESTAMP WITHOUT TIME ZONE ENCODE ZSTD
    ,acct_id_1 VARCHAR(28) ENCODE ZSTD
    ,amt_recon_net NUMERIC(16,2) ENCODE ZSTD
    ,amt_tran NUMERIC(16,2) ENCODE ZSTD
    ,cur_recon_net VARCHAR(3) ENCODE ZSTD
    ,cur_tran VARCHAR(3) ENCODE ZSTD
    ,card_acpt_city VARCHAR(27) ENCODE ZSTD
    ,card_acpt_name VARCHAR(25) ENCODE ZSTD
    ,card_acpt_term_id VARCHAR(15) ENCODE ZSTD
    ,inst_id_acq VARCHAR(11) ENCODE ZSTD
    ,inst_id_iss VARCHAR(11) ENCODE ZSTD
    ,merchant_cat_code VARCHAR(4) ENCODE BYTEDICT
    ,priority VARCHAR(1) ENCODE ZSTD
    ,proc_id_acq VARCHAR(8) ENCODE ZSTD
    ,proc_id_iss VARCHAR(8) ENCODE ZSTD
    ,tran_type_id VARCHAR(10) ENCODE ZSTD
    ,case_group VARCHAR(4) ENCODE ZSTD
    ,cashback_amt NUMERIC(16,2) ENCODE ZSTD
    ,cur_cashback VARCHAR(3) ENCODE ZSTD
    ,account_type VARCHAR(4) ENCODE ZSTD
    ,amt_adjustment NUMERIC(16,2) ENCODE ZSTD
    ,cur_adjustment VARCHAR(3) ENCODE ZSTD
    ,action_to_cardhldr VARCHAR(1) ENCODE ZSTD
    ,adjustment_reason VARCHAR(254) ENCODE ZSTD
    ,reason_code VARCHAR(4) ENCODE BYTEDICT
    ,fraud_reason VARCHAR(1) ENCODE ZSTD
    ,tstamp_created TIMESTAMP WITHOUT TIME ZONE ENCODE ZSTD
    ,transition_user_id VARCHAR(8) ENCODE ZSTD
    ,request_type_prev VARCHAR(4) ENCODE ZSTD
    ,status_prev VARCHAR(4) ENCODE ZSTD
    ,request_type_next VARCHAR(4) ENCODE ZSTD
    ,status_next VARCHAR(4) ENCODE ZSTD
    ,accounting_type VARCHAR(5) ENCODE ZSTD
    ,accounting_user_id VARCHAR(8) ENCODE ZSTD
    ,debit_acct_id VARCHAR(28) ENCODE ZSTD
    ,credit_acct_id VARCHAR(28) ENCODE ZSTD
    ,amount_val NUMERIC(16,2) ENCODE ZSTD
    ,switch_name VARCHAR(10) ENCODE ZSTD
    ,assgn_id VARCHAR(20) ENCODE ZSTD
    ,src_file_name VARCHAR(250) ENCODE ZSTD
    ,load_time TIMESTAMP WITHOUT TIME ZONE ENCODE ZSTD
)
DISTSTYLE KEY
 DISTKEY (pan)
 SORTKEY (
    tstamp_local
    );

Source table:

CREATE TABLE test.padb_dn_ems_activity_history_kush
(
 tstamp_trans character varying(16)
,tstamp_local character varying(14)
,pan character varying(131)
,retrieval_ref_no character varying(12)
,case_type_ind character varying(2)
,sys_trace_audit_no character varying(6)
,case_no character varying(14)
,net_id_ems character varying(3)
,net_term_id character varying(8)
,rpt_lvl_id_b character varying(10)
,status character varying(4)
,request_type character varying(4)
,state_tstamp character varying(16)
,state_expir_tstamp character varying(16)
,acct_id_1 character varying(28)
,amt_recon_net numeric(18,2)
,amt_tran numeric(18,2)
,cur_recon_net character varying(3)
,cur_tran character varying(3)
,card_acpt_city character varying(27)
,card_acpt_name character varying(25)
,card_acpt_term_id character varying(15)
,inst_id_acq character varying(11)
,inst_id_iss character varying(11)
,merchant_cat_code character varying(4)
,priority character varying(1)
,proc_id_acq character varying(8)
,proc_id_iss character varying(8)
,tran_type_id character varying(10)
,case_group character varying(4)
,cashback_amt numeric(20,2)
,cur_cashback character varying(3)
,account_type character varying(4)
,amt_adjustment numeric(18,2)
,cur_adjustment character varying(3)
,action_to_cardhldr character varying(1)
,adjustment_reason character varying(254)
,reason_code character varying(4)
,fraud_reason character varying(1)
,tstamp_created character varying(16)
,transition_user_id character varying(8)
,request_type_prev character varying(4)
,status_prev character varying(4)
,request_type_next character varying(4)
,status_next character varying(4)
,accounting_type character varying(5)
,accounting_user_id character varying(8)
,debit_acct_id character varying(28)
,credit_acct_id character varying(28)
,amount_val numeric(18,2)
,switch_name character varying(10)
);

Function definition:

create or replace
function fdatetime(
    varchar(20)
) returns timestamp stable as $$ select
    case
        when length($1) > 14 then cast(to_timestamp(substring($1, 1, 4)+ '-' + substring($1, 5, 2)+ '-' + substring($1, 7, 2)+ ' ' + substring($1, 9, 2)+ ':' + substring($1, 11, 2)+ ':' + substring($1, 13, 2)+ '.' + substring($1, 15, length($1)-14), 'YYYY-MM-DD HH24:MI:SS:MS') as timestamp)
        when length($1) = 14 then cast(to_timestamp(substring($1, 1, 4)+ '-' + substring($1, 5, 2)+ '-' + substring($1, 7, 2)+ ' ' + substring($1, 9, 2)+ ':' + substring($1, 11, 2)+ ':' + substring($1, 13, 2), 'YYYY-MM-DD HH24:MI:SS:MS') as timestamp)
        when length($1) = 8 then cast(to_timestamp(substring($1, 1, 4)+ '-' + substring($1, 5, 2)+ '-' + substring($1, 7, 2), 'YYYY-MM-DD HH24:MI:SS:MS') as timestamp)
        else null
    end $$ language sql;

And the final SELECT INTO INSERT statement:

INSERT INTO
  test.dn_ems_activity_kush(
    tstamp_trans,
    tstamp_local,
    pan,
    retrieval_ref_no,
    case_type_ind,
    sys_trace_audit_no,
    case_no,
    net_id_ems,
    net_term_id,
    rpt_lvl_id_b,
    status,
    request_type,
    state_tstamp,
    state_expir_tstamp,
    acct_id_1,
    amt_recon_net,
    amt_tran,
    cur_recon_net,
    cur_tran,
    card_acpt_city,
    card_acpt_name,
    card_acpt_term_id,
    inst_id_acq,
    inst_id_iss,
    merchant_cat_code,
    priority,
    proc_id_acq,
    proc_id_iss,
    tran_type_id,
    case_group,
    cashback_amt,
    cur_cashback,
    account_type,
    amt_adjustment,
    cur_adjustment,
    action_to_cardhldr,
    adjustment_reason,
    reason_code,
    fraud_reason,
    tstamp_created,
    transition_user_id,
    request_type_prev,
    status_prev,
    request_type_next,
    status_next,
    accounting_type,
    accounting_user_id,
    debit_acct_id,
    credit_acct_id,
    amount_val,
    switch_name
  )
SELECT
  fdatetime(tstamp_trans),
  fdatetimeintostring(tstamp_local),
  pan,
  retrieval_ref_no,
  case_type_ind,
  sys_trace_audit_no,
  case_no,
  net_id_ems,
  net_term_id,
  rpt_lvl_id_b,
  status,
  request_type,
  fdatetime(state_tstamp),
  fdatetime(state_expir_tstamp),
  acct_id_1,
  ROUND(amt_recon_net / 100, 2),
  Round(amt_tran / 100, 2),
  cur_recon_net,
  cur_tran,
  card_acpt_city,
  card_acpt_name,
  card_acpt_term_id,
  inst_id_acq,
  inst_id_iss,
  merchant_cat_code,
  priority,
  proc_id_acq,
  proc_id_iss,
  tran_type_id,
  case_group,
  Round(cashback_amt / 100, 2) cashback_amt,
  cur_cashback
  account_type,
  Round(amt_adjustment / 100, 2),
  cur_adjustment,
  action_to_cardhldr,
  adjustment_reason,
  reason_code,
  fraud_reason,
  fdatetime(tstamp_created),
  transition_user_id,
  request_type_prev,
  status_prev,
  request_type_next,
  status_next,
  accounting_type,
  accounting_user_id,
  debit_acct_id,
  credit_acct_id,
  Round(amount_val / 100, 2),
  switch_name
FROM
  test.padb_dn_ems_activity_history_kush;

SQL Error [500310] [42804]: Amazon Invalid operation: column "tstamp_created" is of type timestamp without time zone but expression is of type character varying;

Answer 1

It's just a syntax error here. In my select clause after column cur_cashback, I forgot to place a comma. And that's why there was an error.