1

I'm trying to insert values in a table that resides in RedShift database. But when i insert the NULL value in an integer column i get the following error:

Amazon Invalid operation: column "INVC_RLS_LCTN " is of type integer but expression is of type character varying;

Here is the schema:

CREATE TABLE DM_TX_LINE_FCT
(
SRRGT_ID BIGINT NOT NULL,
IGT_RSRVTN_ID CHARACTER VARYING(40),
INVC_RLS_LCTN INTEGER,
)
distkey(TX_SRRGT_KEY)
SORTKEY(LCTN_ID, PRCSSNG_DT_KEY);

I'm inserting in the table like this 

 ...
 ...                     
     PT.CASHIER_NBR,
     PT.MMBRSHP_CARD_ID,
     MMBR.MMBRSHP_CARD_SRRGT_ID,
     NULL as IGT_RSRVTN_ID,
     NULL as INVC_RLS_LCTN,  
 ... 
 ...

Can anyone tell me, why can't i store NULL in integer value?

Aneela Saleem Ramzan
  • 127
  • 1
  • 8
  • 15

1 Answers1

0

The database is guessing the NULL value's type as a VARCHAR. Probably because of something else happening in the query.

Explicitly cast it to INTEGER to allow insertion.

CREATE TEMP TABLE "nulltest" ("nulltest" INT);
    --CREATE TABLE

INSERT INTO "nulltest" SELECT CAST(NULL AS VARCHAR(10));
    --ERROR:  column "nulltest" is of type integer but expression is of type character varying
    --HINT:  You will need to rewrite or cast the expression.

INSERT INTO "nulltest" SELECT CAST(NULL AS INTEGER);
    --INSERT 0 1
Joe Harris
  • 13,671
  • 4
  • 47
  • 54
  • can you please help me resolve this https://stackoverflow.com/questions/54720771/amazon500310-invalid-operation-this-type-of-in-not-in-query-is-not-supporte – Aneela Saleem Ramzan Feb 24 '19 at 18:45