Postgres Pro
Downloads
Home   >   mailing lists

Thread: data type - import problem

data type - import problem

From
Kirk Wythers
Date:

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.

ROWIDTIME2TIMESTAMPBLOCKSTATNAMETABLEPROGRAMA_TC_AVG1A_TC_AVG10A_TC_AVG11A_TC_AVG12A_TC_AVG2A_TC_AVG3A_TC_AVG4A_TC_AVG5A_TC_AVG6A_TC_AVG7A_TC_AVG8A_TC_AVG9AIRTC_AVGECODE1ECODE10ECODE11ECODE12ECODE2ECODE3ECODE4ECODE5ECODE6ECODE7ECODE8ECODE9ETC_AVG13ETC_AVG14ETC_AVG15ETC_AVG16ETC_AVG17ETC_AVG18ETC_AVG19ETC_AVG20ETC_AVG21ETC_AVG22ETC_AVG23ETC_AVG24ETC_AVG25FLAG1FLAG10FLAG11FLAG12FLAG2FLAG3FLAG4FLAG5FLAG6FLAG7FLAG8FLAG9PAR_DEN_AVGPAR_TOT_TOTPERIOD10PERIOD11PERIOD12PERIOD13PERIOD14PERIOD15PERIOD16PERIOD9RAIN_IN_TOTRHS_TC_AVG1S_TC_AVG10S_TC_AVG11S_TC_AVG12S_TC_AVG2S_TC_AVG3S_TC_AVG4S_TC_AVG5S_TC_AVG6S_TC_AVG7S_TC_AVG8S_TC_AVG9SLR_MJ_TOTSLR_W_AVGSOILTEMP10_AVGSOILTEMP100_AVGSOILTEMP20_AVGSOILTEMP30_AVGSOILTEMP40_AVGSOILTEMP50_AVGSOILTEMP75_AVGVP_AVGVWC10VWC11VWC12VWC13VWC14VWC15VWC16VWC9WCODE1WCODE10WCODE11WCODE12WCODE2WCODE3WCODE4WCODE5WCODE6WCODE7WCODE8WCODE9WTC_AVG13WTC_AVG14WTC_AVG15WTC_AVG16WTC_AVG17WTC_AVG18WTC_AVG19WTC_AVG20WTC_AVG21WTC_AVG22WTC_AVG23WTC_AVG24WTC_AVG25
2012-03-22 21:00:00_B4WARM_A13324716001332471600   B4WARM_AB4WARM_AB4Warm_60CPU:B4warm2011_Sauron_ALT4.2.CR1NANANANANANANANANANANANA0NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA0025.6324.7323.2723.8723.6325.0923.5921.7600NANANANANANANANANANANANA00NANANANANANANA00.2320.2060.1660.1820.1760.2160.1750.128NANANANANANANANANANANANANANANANANANANANANANANANANA

Re: data type - import problem

From
Adrian Klaver
Date:
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

Re: data type - import problem

From
Kirk Wythers
Date:
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. 


ROWIDTIME2TIMESTAMPBLOCKSTATNAMETABLEPROGRAMA_TC_AVG1A_TC_AVG10A_TC_AVG11A_TC_AVG12A_TC_AVG2A_TC_AVG3A_TC_AVG4A_TC_AVG5A_TC_AVG6A_TC_AVG7A_TC_AVG8A_TC_AVG9AIRTC_AVGECODE1ECODE10ECODE11ECODE12ECODE2ECODE3ECODE4ECODE5ECODE6ECODE7ECODE8ECODE9ETC_AVG13ETC_AVG14ETC_AVG15ETC_AVG16ETC_AVG17ETC_AVG18ETC_AVG19ETC_AVG20ETC_AVG21ETC_AVG22ETC_AVG23ETC_AVG24ETC_AVG25FLAG1FLAG10FLAG11FLAG12FLAG2FLAG3FLAG4FLAG5FLAG6FLAG7FLAG8FLAG9PAR_DEN_AVGPAR_TOT_TOTPERIOD10PERIOD11PERIOD12PERIOD13PERIOD14PERIOD15PERIOD16PERIOD9RAIN_IN_TOTRHS_TC_AVG1S_TC_AVG10S_TC_AVG11S_TC_AVG12S_TC_AVG2S_TC_AVG3S_TC_AVG4S_TC_AVG5S_TC_AVG6S_TC_AVG7S_TC_AVG8S_TC_AVG9SLR_MJ_TOTSLR_W_AVGSOILTEMP10_AVGSOILTEMP100_AVGSOILTEMP20_AVGSOILTEMP30_AVGSOILTEMP40_AVGSOILTEMP50_AVGSOILTEMP75_AVGVP_AVGVWC10VWC11VWC12VWC13VWC14VWC15VWC16VWC9WCODE1WCODE10WCODE11WCODE12WCODE2WCODE3WCODE4WCODE5WCODE6WCODE7WCODE8WCODE9WTC_AVG13WTC_AVG14WTC_AVG15WTC_AVG16WTC_AVG17WTC_AVG18WTC_AVG19WTC_AVG20WTC_AVG21WTC_AVG22WTC_AVG23WTC_AVG24WTC_AVG25
2012-03-22 21:00:00_B4WARM_A13324716001332471600   B4WARM_AB4WARM_AB4Warm_60CPU:B4warm2011_Sauron_ALT4.2.CR1NANANANANANANANANANANANA0NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA0025.6324.7323.2723.8723.6325.0923.5921.7600NANANANANANANANANANANANA00NANANANANANANA00.2320.2060.1660.1820.1760.2160.1750.128NANANANANANANANANANANANANANANANANANANANANANANANANA




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

Re: data type - import problem

From
Adrian Klaver
Date:
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

Re: data type - import problem

From
Steve Crawford
Date:
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