Обсуждение: data type - import problem
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.
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 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
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
On 12/17/2012 05:30 PM, Kirk Wythers wrote: > 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. > Either preprocess the data to turn NA into NULL or put the data into a staging table that is looser about types (i.e. use varchar for this column) and then transform in place and then move over into the final table. -- Adrian Klaver adrian.klaver@gmail.com
On 12/17/2012 05:46 PM, Adrian Klaver wrote: > On 12/17/2012 05:30 PM, Kirk Wythers wrote: >> 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. >> > > Either preprocess the data to turn NA into NULL or put the data into a > staging table that is looser about types (i.e. use varchar for this > column) and then transform in place and then move over into the final > table. > > Although it may be overkill for a simple case like this, especially if it is a one-time load, there are plenty of open-source ETL tools out there: https://www.google.com/search?q=open+source+etl Cheers, Steve