Thread: COPY and custom datestyles. Or some other technique?

Postgresql 13.10

$ psql -h myhost.example.com -X dba \
         -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH 
DELIMITER '|';"
ERROR:  date/time field value out of range: "2013061914122501"
CONTEXT:  COPY t_id_master, line 1, column update_timestamp: "2013061914122501"

The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC (year to 
centisecond, with no delimiters).

Is there any way to convince Postgresql to import these fields?

There are 550+ tables, so something that I can do once on this end would 
make my life a lot easier.

-- 
Born in Arizona, moved to Babylonia.

On 3/29/23 16:24, Ron wrote:
> Postgresql 13.10
> 
> $ psql -h myhost.example.com -X dba \
>          -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH 
> DELIMITER '|';"
> ERROR:  date/time field value out of range: "2013061914122501"
> CONTEXT:  COPY t_id_master, line 1, column update_timestamp: 
> "2013061914122501"
> 
> The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC 
> (year to centisecond, with no delimiters).
> 
> Is there any way to convince Postgresql to import these fields?

One option:

1) Import into staging table as varchar field.

2) Use to_timestamp()(NOTE change in template pattern) from here:

https://www.postgresql.org/docs/current/functions-formatting.html

select to_timestamp('2013061914122501', 'YYYYMMDDHH24miSSCC');
       to_timestamp
-------------------------
  06/19/2013 14:12:25 PDT

to move the data into final table.

> 
> There are 550+ tables, so something that I can do once on this end would 
> make my life a lot easier.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/29/23 16:31, Adrian Klaver wrote:
> On 3/29/23 16:24, Ron wrote:
>> Postgresql 13.10
>>
>> $ psql -h myhost.example.com -X dba \
>>          -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH 
>> DELIMITER '|';"
>> ERROR:  date/time field value out of range: "2013061914122501"
>> CONTEXT:  COPY t_id_master, line 1, column update_timestamp: 
>> "2013061914122501"
>>
>> The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC 
>> (year to centisecond, with no delimiters).
>>
>> Is there any way to convince Postgresql to import these fields?
> 
> One option:
> 
> 1) Import into staging table as varchar field.
> 
> 2) Use to_timestamp()(NOTE change in template pattern) from here:
> 
> https://www.postgresql.org/docs/current/functions-formatting.html
> 
> select to_timestamp('2013061914122501', 'YYYYMMDDHH24miSSCC');
>        to_timestamp
> -------------------------
>   06/19/2013 14:12:25 PDT

Actually it probably should be:

select to_timestamp('2013061914122501', 'YYYYMMDDHH24miSSMS');
         to_timestamp
----------------------------
  06/19/2013 14:12:25.01 PDT

> 
> to move the data into final table.
> 
>>
>> There are 550+ tables, so something that I can do once on this end 
>> would make my life a lot easier.
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 29 Mar 2023, Ron wrote:

> There are 550+ tables, so something that I can do once on this end would make
> my life a lot easier.

Some quick perl or awk or shell job to batch-change the field
to an accepted syntax is probably quicker.

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

On 3/29/23 19:20, Thorsten Glaser wrote:
> On Wed, 29 Mar 2023, Ron wrote:
>
>> There are 550+ tables, so something that I can do once on this end would make
>> my life a lot easier.
> Some quick perl or awk or shell job to batch-change the field
> to an accepted syntax is probably quicker.

Even easier would be to create views on the source which cast from DATE VMS 
to TIMESTAMP(2).

Whatever the method, it all added up to lots places where I can make an 
error or overlook something.

-- 
Born in Arizona, moved to Babylonia.

On 3/29/23 18:31, Adrian Klaver wrote:
> On 3/29/23 16:24, Ron wrote:
>> Postgresql 13.10
>>
>> $ psql -h myhost.example.com -X dba \
>>          -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH 
>> DELIMITER '|';"
>> ERROR:  date/time field value out of range: "2013061914122501"
>> CONTEXT:  COPY t_id_master, line 1, column update_timestamp: 
>> "2013061914122501"
>>
>> The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC (year 
>> to centisecond, with no delimiters).
>>
>> Is there any way to convince Postgresql to import these fields?
>
> One option:
>
> 1) Import into staging table as varchar field.

I had thought of that.

It would be really helpful to be able to reposition columns in tables.  That 
way, one could:
add the new TIMESTAMP column,
populate it using to_timestamp(),
drop the text column,
reposition the TIMESTAMP column to where it "should" be.

-- 
Born in Arizona, moved to Babylonia.

On Wed, Mar 29, 2023 at 6:51 PM Ron <ronljohnsonjr@gmail.com> wrote:

It would be really helpful to be able to reposition columns in tables.  That
way, one could:
add the new TIMESTAMP column,
populate it using to_timestamp(),
drop the text column,
reposition the TIMESTAMP column to where it "should" be.

If that would give you what you need then just define the column as text initially, load the data, then do an ALTER COLUMN ... ALTER TYPE to change the column type to timestamptz in place, with the conversion done via USING.

David J.