[SQL] 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/, and I'm no able to copy this table into postgres... because it needs /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: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] date problems
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/, > and I'm no able to copy this table into postgres... because it needs > /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/') 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] date problems
On Aug 30, 2007, at 8:01 , ivan marchesini wrote: a lot of table have some timestamp fields containing data as DD/MM/ , and I'm no able to copy this table into postgres... because it needs /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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] date problems
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/, and I'm no able to copy this table into postgres... because it needs /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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Reporting functions (OLAP)
I am looking for reporting extensions such as windowing, ranking, leads/lags, etc. for postgresql. A quick google search turned up some "working on it" type results, but I was wondering if anything actually existed up to this point? Thanks, Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Software CASE - PostgreSQL
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
