CODEWITHSUNDEEP
sqlpostgresqlamazon-web-servicesamazon-redshift
jimy
jimy

Reputation: 4908

Data value "0" has invalid format error in redshift

We are facing a weird problem with one of our query. Below is the query we are running 

INSERT into test 
SELECT 
    member.name as mem_name, 
    CASE WHEN ( member.dob>0 AND length (member.dob)=8 ) THEN (DATEDIFF(year,to_date("dob",'YYYYMMDD'), to_date(20140716,'YYYYMMDD'))) WHEN ( member.dob=0 ) Then 0 END As Age, 
    20140716021501 
FROM 
    member

Below is the sample data present in our table.

|name           |dob
|Ajitsh         |0        |
|rk             |51015    |
|s_thiagarajan  |19500130 |
|madhav_7       |19700725 |
|1922           |0        |
|rekha          |25478    |
|vmkurup        |0        |
|ravikris       |19620109 |
|ksairaman      |0        |
|sruthi         |0        |
|rrbha          |19630825 |
|sunilsw        |0        |
|sunilh         |0        |
|venky_pmv      |19701207 |
|malagi         |0        |
|an752001       |0        |
|edsdf          |19790201 |
|anuanand       |19730724 |
|fresh          |19720821 |
|ampharcopharma |19590127 |
|Nanze          |19621123 |

The date of birth is stored in bigint as YYYYMMDD format. In the data there are some rows, in which date is invalid like 0, 51015. On some instances this query raises the following error.

INSERT INTO test not successful
An error occurred when executing the SQL command:
INSERT into test 
SELECT 
    member.name as mem_name, 
    CASE WHEN ( member.dob>0 AND length (member.dob)=8 ) THEN (DATEDIFF(y...

ERROR: Data value "0" has invalid format
    Detail: 
    -----------------------------------------------
    error:  Data value "0" has invalid format
    code:      1009
    context:   PG ERROR
    query:     92776
    location:  pg_utils.cpp:2731
    process:   query1_30 [pid=1434]
    -----------------------------------------------


Execution time: 3.99s

1 statement failed.

But the strange thing is, it raises the error randomly and not all the time. Many times it works without any change in query or dataset. Sometime it also works in second or third attempt. My doubt is that to_date function is giving this error. But why randomly and not gives error on every run.

To support my assumption I also tried this small query.
SELECT to_date(20140716,'YYYYMMDD'), to_date(0,'YYYYMMDD');
But this also creates the same scenario. It raises error randomly, while runs smoothly rest of the times.

Upvotes: 3

Views: 2824

Answers (1)

Ilesh Patel
Ilesh Patel

Reputation: 2155

If is it fine to ignore this type of values and just convert this to Date format you can follow the below way.

SELECT to_date('20140716','YYYYMMDD'), to_date('0','FMYYYYMMDD');

Here FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width.

Upvotes: 1

Related Questions