Re: data type - import problem - Mailing list pgsql-general
| From | Kirk Wythers |
|---|---|
| Subject | Re: data type - import problem |
| Date | |
| Msg-id | 4BB86E54-D593-4143-9485-AB0F0257465D@gmail.com Whole thread Raw |
| In response to | Re: data type - import problem (Adrian Klaver <adrian.klaver@gmail.com>) |
| Responses |
Re: data type - import problem
|
| List | pgsql-general |
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.klaver@gmail.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.klaver@gmail.com
pgsql-general by date: