pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate problems
4+ messages / 4 participants
[nested] [flat]
* date problems
@ 2007-08-30 13:01 ivan marchesini <marchesini@unipg.it>
2007-08-30 13:29 ` Re: date problems A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-08-30 14:39 ` Re: date problems Michael Glaesemann <grzm@seespotcode.net>
2007-08-30 18:31 ` Re: date problems Roberto Spier <roberto.spier@conab.gov.br>
0 siblings, 3 replies; 4+ messages in thread
From: ivan marchesini @ 2007-08-30 13:01 UTC (permalink / raw)
To: pgsql-sql
Dear PG users,
I have an access db and I'm trying to pass all to postgres
I have used mdbtools to export the schema and all went quite well.
then I exported the single tables... to csv..
a lot of table have some timestamp fields containing data as DD/MM/YYYY,
and I'm no able to copy this table into postgres... because it needs
YYYY/MM/DD...
I used \copy...
Which is the best solution for this problem...
many thanks
Ivan
--
Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg.
Preferisco formati liberi.
Please try to avoid to send me .doc, .xls, .ppt, .dwg files.
I prefer free formats.
http://it.wikipedia.org/wiki/Formato_aperto
http://en.wikipedia.org/wiki/Open_format
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a
06125
Perugia (Italy)
e-mail: marchesini@unipg.it
ivan.marchesini@gmail.com
tel: +39(0)755853760
fax (university): +39(0)755853756
fax (home): +39(0)5782830887
jabber: geoivan73@jabber.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date problems
2007-08-30 13:01 date problems ivan marchesini <marchesini@unipg.it>
@ 2007-08-30 13:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2007-08-30 13:29 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 30.08.2007, um 15:01:03 +0200 mailte ivan marchesini folgendes:
> Dear PG users,
> I have an access db and I'm trying to pass all to postgres
> I have used mdbtools to export the schema and all went quite well.
> then I exported the single tables... to csv..
>
> a lot of table have some timestamp fields containing data as DD/MM/YYYY,
> and I'm no able to copy this table into postgres... because it needs
> YYYY/MM/DD...
> I used \copy...
>
> Which is the best solution for this problem...
Change this in the csv-file with text-tools like sed or awk. Other
solution: use a temp. table for input and use to_date to convert,
simple example:
test=> create table e (d date);
CREATE TABLE
test=*> create table e_temp (d text);
CREATE TABLE
test=*> copy e_temp from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 30/08/2007
>> \.
test=*> insert into e select to_date(d, 'dd/mm/yyyy') from e_temp;
INSERT 0 1
test=*> select * from e;
d
------------
2007-08-30
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date problems
2007-08-30 13:01 date problems ivan marchesini <marchesini@unipg.it>
@ 2007-08-30 14:39 ` Michael Glaesemann <grzm@seespotcode.net>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Glaesemann @ 2007-08-30 14:39 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: pgsql-sql
On Aug 30, 2007, at 8:01 , ivan marchesini wrote:
> a lot of table have some timestamp fields containing data as DD/MM/
> YYYY,
> and I'm no able to copy this table into postgres... because it needs
> YYYY/MM/DD...
> I used \copy...
The input and output formats of dates is controlled by the datestyle
setting. You can easily change this to allow COPY (and I assume
\copy) to load the dates in their current format.
test=# create table dates (a_date date primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"dates_pkey" for table "dates"
CREATE TABLE
test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2007/08/30
>> 2007/08/29
>> \.
No problem loading dates in YMD.
test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 08/28/2007
>> \.
No problem with MDY.
test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 27/08/2007
>> \.
ERROR: date/time field value out of range: "27/08/2007"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY dates, line 1, column a_date: "27/08/2007"
DMY fails.
test=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
The current datestyle is ISO for output and MDY for input. This
explains why '27/08/2007' failed.
test=# set datestyle to 'iso, dmy'; -- output still iso, input day-
month-year
SET
test=# show datestyle;
DateStyle
-----------
ISO, DMY
(1 row)
test=# copy dates (a_date) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 27/08/2007
>> \.
Since the datestyle was changed, we can now input '27/08/2007'.
test=# select * from dates;
a_date
------------
2007-08-30
2007-08-29
2007-08-28
2007-08-27
(4 rows)
And there they are: all output in ISO format.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: date problems
2007-08-30 13:01 date problems ivan marchesini <marchesini@unipg.it>
@ 2007-08-30 18:31 ` Roberto Spier <roberto.spier@conab.gov.br>
2 siblings, 0 replies; 4+ messages in thread
From: Roberto Spier @ 2007-08-30 18:31 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: pgsql-sql
ivan marchesini escreveu:
> Dear PG users,
> I have an access db and I'm trying to pass all to postgres
> I have used mdbtools to export the schema and all went quite well.
> then I exported the single tables... to csv..
>
> a lot of table have some timestamp fields containing data as DD/MM/YYYY,
> and I'm no able to copy this table into postgres... because it needs
> YYYY/MM/DD...
> I used \copy...
>
> Which is the best solution for this problem...
>
> many thanks
>
> Ivan
>
>
another approach:
from within your access .mdb file, connect your new pg tables through
ODBC. Then execute an INSERT INTO pg_table(...) SELECT ... FROM acc_table;
spier
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-30 18:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-30 13:01 date problems ivan marchesini <marchesini@unipg.it>
2007-08-30 13:29 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-08-30 14:39 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-30 18:31 ` Roberto Spier <roberto.spier@conab.gov.br>
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