pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY 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