pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
COPY and custom datestyles. Or some other technique?
9+ messages / 4 participants
[nested] [flat]

* COPY and custom datestyles. Or some other technique?
@ 2023-03-29 23:24  Ron <ronljohnsonjr@gmail.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Ron @ 2023-03-29 23:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-29 23:31  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  1 sibling, 2 replies; 9+ messages in thread

From: Adrian Klaver @ 2023-03-29 23:31 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-29 23:40  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Adrian Klaver @ 2023-03-29 23:40 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 00:20  Thorsten Glaser <tg@evolvis.org>
  parent: Ron <ronljohnsonjr@gmail.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Thorsten Glaser @ 2023-03-30 00:20 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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 :-)





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 01:44  Ron <ronljohnsonjr@gmail.com>
  parent: Thorsten Glaser <tg@evolvis.org>
  0 siblings, 0 replies; 9+ messages in thread

From: Ron @ 2023-03-30 01:44 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 01:50  Ron <ronljohnsonjr@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Ron @ 2023-03-30 01:50 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 02:06  David G. Johnston <david.g.johnston@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: David G. Johnston @ 2023-03-30 02:06 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 02:38  Ron <ronljohnsonjr@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Ron @ 2023-03-30 02:38 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 3/29/23 21:06, David G. Johnston wrote:
> 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.
>

Something like this?

ALTER TABLE foo
     ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE
         USING to_timestamp(update_ts, 'YYYYMMDDHH24miSSMS');

That would definitely minimize the possibility of errors.

-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: COPY and custom datestyles. Or some other technique?
@ 2023-03-30 03:03  David G. Johnston <david.g.johnston@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: David G. Johnston @ 2023-03-30 03:03 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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

> Something like this?
>
> ALTER TABLE foo
>     ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE
>         USING to_timestamp(update_ts, 'YYYYMMDDHH24miSSMS');
>
> That would definitely minimize the possibility of errors.
>

Yes.

David J.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2023-03-30 03:03 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-29 23:24 COPY and custom datestyles. Or some other technique? Ron <ronljohnsonjr@gmail.com>
2023-03-29 23:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-29 23:40   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-30 01:50   ` Ron <ronljohnsonjr@gmail.com>
2023-03-30 02:06     ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-30 02:38       ` Ron <ronljohnsonjr@gmail.com>
2023-03-30 03:03         ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-30 00:20 ` Thorsten Glaser <tg@evolvis.org>
2023-03-30 01:44   ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox