Обсуждение: date problems
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
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
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
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
Hi ! What CASE tool may use with Postgresql? I need create MER diagram. I am a big problem with Erwin to create MER diagram of PostgreSQL. Thanks,. Eduardo