Re: data type - import problem

Thanks.

Is there a way to use the float4 data type for a variable when empty records contain "NA", instead of nothing? As you can see below the variable "A_TC_AVG1" has the first record filled with "NA" signifying that the record is empty? Records that contain values will have decimal values like 12.3456.

ROWID TIME2 TIMESTAMP BLOCK STATNAME TABLE PROGRAM A_TC_AVG1 A_TC_AVG10 A_TC_AVG11 A_TC_AVG12 A_TC_AVG2 A_TC_AVG3 A_TC_AVG4 A_TC_AVG5 A_TC_AVG6 A_TC_AVG7 A_TC_AVG8 A_TC_AVG9 AIRTC_AVG ECODE1 ECODE10 ECODE11 ECODE12 ECODE2 ECODE3 ECODE4 ECODE5 ECODE6 ECODE7 ECODE8 ECODE9 ETC_AVG13 ETC_AVG14 ETC_AVG15 ETC_AVG16 ETC_AVG17 ETC_AVG18 ETC_AVG19 ETC_AVG20 ETC_AVG21 ETC_AVG22 ETC_AVG23 ETC_AVG24 ETC_AVG25 FLAG1 FLAG10 FLAG11 FLAG12 FLAG2 FLAG3 FLAG4 FLAG5 FLAG6 FLAG7 FLAG8 FLAG9 PAR_DEN_AVG PAR_TOT_TOT PERIOD10 PERIOD11 PERIOD12 PERIOD13 PERIOD14 PERIOD15 PERIOD16 PERIOD9 RAIN_IN_TOT RH S_TC_AVG1 S_TC_AVG10 S_TC_AVG11 S_TC_AVG12 S_TC_AVG2 S_TC_AVG3 S_TC_AVG4 S_TC_AVG5 S_TC_AVG6 S_TC_AVG7 S_TC_AVG8 S_TC_AVG9 SLR_MJ_TOT SLR_W_AVG SOILTEMP10_AVG SOILTEMP100_AVG SOILTEMP20_AVG SOILTEMP30_AVG SOILTEMP40_AVG SOILTEMP50_AVG SOILTEMP75_AVG VP_AVG VWC10 VWC11 VWC12 VWC13 VWC14 VWC15 VWC16 VWC9 WCODE1 WCODE10 WCODE11 WCODE12 WCODE2 WCODE3 WCODE4 WCODE5 WCODE6 WCODE7 WCODE8 WCODE9 WTC_AVG13 WTC_AVG14 WTC_AVG15 WTC_AVG16 WTC_AVG17 WTC_AVG18 WTC_AVG19 WTC_AVG20 WTC_AVG21 WTC_AVG22 WTC_AVG23 WTC_AVG24 WTC_AVG25
2012-03-22 21:00:00_B4WARM_A 1332471600 1332471600 B4WARM_A B4WARM_A B4Warm_60 CPU:B4warm2011_Sauron_ALT4.2.CR1 NA NA NA NA NA NA NA NA NA NA NA NA 0 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 0 25.63 24.73 23.27 23.87 23.63 25.09 23.59 21.76 0 0 NA NA NA NA NA NA NA NA NA NA NA NA 0 0 NA NA NA NA NA NA NA 0 0.232 0.206 0.166 0.182 0.176 0.216 0.175 0.128 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

On Dec 17, 2012, at 4:05 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> On 12/17/2012 01:53 PM, Kirk Wythers wrote:
>>
>> I seem to be dealing with a data type issue when I try and import data
>> into a new and empty database.
>>
>> Error Message: ERROR: date/time field value out of range:
>> "1332471600:00:00"
>> LINE 1: ...tc_avg25") values ('2012-03-22 21:00:00_B4WARM_A','133247160…
>>
>> Here are the first two rows in the files I was sent. I cast ROWID as
>> varchar32, TIME2 as time (I was told that TIME2 was a posix time value),
>> and TIMESTAMP as timestamp.
>>
>> Any advice that would help me out of this hole is appreciated.
>
> Assuming by POSIX time you mean seconds since epoch you will need to do something like this:
>
> test=# SELECT to_timestamp(1332471600);
> to_timestamp
> ------------------------
> 2012-03-22 20:00:00-07
>
> For time:
> test=# SELECT to_timestamp('1332471600')::time;
> to_timestamp
> --------------
> 20:00:00
>
>
> In your INSERT statement use the to_timestamp() in the VALUES portion.
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com