Thread: how to convert Access to PG unless odbc driver(with sql file)
I wont to convert an Access database to a PG on different computer and i
don't have network connection. I try to use exportSQL2+pg.txt but don't work!
the head of file generated from Access database is:
-- Exported from MS Access to mSQL
-- (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net
CREATE TABLE DATI
(
ESERCIZIO varchar(100),
VIA_E_NUMERO varchar(61),
LOCALITA' varchar(50),
TELEFONO varchar(30),
TIPOLOGIA varchar(50),
ORARIO_DI_APERTURA varchar(100),
RIPOSO varchar(51),
A1 varchar(2),
B1 varchar(2),
B2 varchar(10),
B3 varchar(3),
B4 varchar(30),
B5 varchar(5),
B6 varchar(2),
B7 varchar(4),
B8 varchar(2),
B9 varchar(15),
B10 varchar(4),
B11 varchar(2),
B12 varchar(3),
B13 varchar(2),
B14 varchar(20),
B15 varchar(3),
C1 varchar(10),
C2 varchar(2),
C3 varchar(2),
C6 varchar(10),
C7 varchar(15),
D2 varchar(7),
D5 varchar(5),
E2 varchar(11),
E5 varchar(30),
OSSERVAZIONI1 text,
F4 varchar(2),
F5 varchar(2),
F6 varchar(4),
G5 varchar(2),
G6 varchar(2),
G7 varchar(2),
G8 varchar(2),
G9 varchar(2),
G10 varchar(2),
G11 varchar(2),
G12 varchar(2),
G13 varchar(2),
G14 varchar(2),
H1 varchar(3),
H2 varchar(3),
H3 varchar(3),
H4 varchar(3),
H6 varchar(3),
H7 varchar(11),
OSSERVAZIONI2 text,
I1 varchar(2),
I2 varchar(2),
I3 varchar(3),
M1 varchar(20),
M2 varchar(2),
M3 varchar(2),
M8 varchar(50),
M9 varchar(50),
M10 varchar(50),
M11 varchar(50),
OSSERVAZIONI3 text
)\g
INSERT INTO DATI
VALUES (
'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO',
'VIA B. CLESIO 3',
'TRENTO CENTRO',
'0461-230482',
'BIBLIOTECA',
'09.00 - 12.00 14.30 - 16.30',
'LUNEDI\'',
'NO',
'SI',
'18',
'13',
'ESTERNI',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'MANUALE',
'NO',
'SI',
'206',
'INTERNO',
NULL,
'110',
'ORIZZONTALE',
'160',
'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.',
'NO',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'SI',
'NO',
'110',
'91',
'NO',
'SI',
'SI',
'SI',
'1.82',
'1.86',
'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g
INSERT INTO DATI
VALUES (
'SALA DI PUBBLICA LETTURA',
'VIA SALE\' 1',
'POVO',
'0461-810241',
'BIBLIOTECA',
'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30',
'SABATO',
'SI',
'NO',
.......................................................
and the output-error of psql is:
-- Exported from MS Access to mSQL
-- (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net
CREATE TABLE DATI
(
ESERCIZIO varchar(100),
VIA_E_NUMERO varchar(61),
LOCALITA' varchar(50),
TELEFONO varchar(30),
TIPOLOGIA varchar(50),
ORARIO_DI_APERTURA varchar(100),
RIPOSO varchar(51),
A1 varchar(2),
B1 varchar(2),
B2 varchar(10),
B3 varchar(3),
B4 varchar(30),
B5 varchar(5),
B6 varchar(2),
B7 varchar(4),
B8 varchar(2),
B9 varchar(15),
B10 varchar(4),
B11 varchar(2),
B12 varchar(3),
B13 varchar(2),
B14 varchar(20),
B15 varchar(3),
C1 varchar(10),
C2 varchar(2),
C3 varchar(2),
C6 varchar(10),
C7 varchar(15),
D2 varchar(7),
D5 varchar(5),
E2 varchar(11),
E5 varchar(30),
OSSERVAZIONI1 text,
F4 varchar(2),
F5 varchar(2),
F6 varchar(4),
G5 varchar(2),
G6 varchar(2),
G7 varchar(2),
G8 varchar(2),
G9 varchar(2),
G10 varchar(2),
G11 varchar(2),
G12 varchar(2),
G13 varchar(2),
G14 varchar(2),
H1 varchar(3),
H2 varchar(3),
H3 varchar(3),
H4 varchar(3),
H6 varchar(3),
H7 varchar(11),
OSSERVAZIONI2 text,
I1 varchar(2),
I2 varchar(2),
I3 varchar(3),
M1 varchar(20),
M2 varchar(2),
M3 varchar(2),
M8 varchar(50),
M9 varchar(50),
M10 varchar(50),
M11 varchar(50),
OSSERVAZIONI3 text
)\g
INSERT INTO DATI
VALUES (
'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO',
'VIA B. CLESIO 3',
'TRENTO CENTRO',
'0461-230482',
'BIBLIOTECA',
'09.00 - 12.00 14.30 - 16.30',
'LUNEDI\'',
'NO',
'SI',
'18',
'13',
'ESTERNI',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'MANUALE',
'NO',
'SI',
'206',
'INTERNO',
NULL,
'110',
'ORIZZONTALE',
'160',
'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.',
'NO',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'SI',
'NO',
'110',
'91',
'NO',
'SI',
'SI',
'SI',
'1.82',
'1.86',
'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g
ERROR: parser: parse error at or near "'"
INSERT INTO DATI
VALUES (
'SALA DI PUBBLICA LETTURA',
'VIA SALE\' 1',
'POVO',
'0461-810241',
'BIBLIOTECA',
'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30',
'SABATO',
'SI',
.........................................
and in the standard optput there was nothing!
Why?
Thanks
Ludo
Hi All,
Below is a simple table with an index on phonenumber, if I do a select on
phonenumber without quotes then it does a sequence scan?? If I do put
quotes, then it does use the index scan??? Anyone know what's going on?
The version of postgres is 7.1
Regards,
Dave
test=# create table custbase (phonenumber int8, svctype char, svcchoice
char, billtype char, ctype char);
CREATE
test=# create index custbaseidx on custbase (phonenumber);
CREATE
test=# \d custbase
Table "custbase"
Attribute | Type | Modifier
-------------+--------------+----------
phonenumber | bigint |
svctype | character(1) |
svcchoice | character(1) |
billtype | character(1) |
ctype | character(1) |
Index: custbaseidx
test=# explain select * from custbase where phonenumber=5199400858;
NOTICE: QUERY PLAN:
Seq Scan on custbase (cost=0.00..25.00 rows=10 width=56)
EXPLAIN
test=# explain select * from custbase where phonenumber='5199400858';
NOTICE: QUERY PLAN:
Index Scan using custbaseidx on custbase (cost=0.00..8.14 rows=10 width=56)
Postgres is not mSQL. While I don't know what exportSQL2+pg.txt, it apparently has no idea about proper SQL quoting. See lines: > LOCALITA' varchar(50), (you are not allowed to have quotes in field names without quoting entire field "LOCALITA'" and even then I doubt it'd work. > 'LUNEDI\'', You must use 'LUNEDI''' (proper way to quote a single quote is with a double single quote). -alex On Tue, 22 May 2001, Ludovico Romano wrote: > I wont to convert an Access database to a PG on different computer and i > don't have network connection. I try to use exportSQL2+pg.txt but don't work! > the head of file generated from Access database is: > > -- Exported from MS Access to mSQL > -- (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net > > > CREATE TABLE DATI > ( > ESERCIZIO varchar(100), > VIA_E_NUMERO varchar(61), > LOCALITA' varchar(50), > TELEFONO varchar(30), > TIPOLOGIA varchar(50), > ORARIO_DI_APERTURA varchar(100), > RIPOSO varchar(51), > A1 varchar(2), > B1 varchar(2), > B2 varchar(10), > B3 varchar(3), > B4 varchar(30), > B5 varchar(5), > B6 varchar(2), > B7 varchar(4), > B8 varchar(2), > B9 varchar(15), > B10 varchar(4), > B11 varchar(2), > B12 varchar(3), > B13 varchar(2), > B14 varchar(20), > B15 varchar(3), > C1 varchar(10), > C2 varchar(2), > C3 varchar(2), > C6 varchar(10), > C7 varchar(15), > D2 varchar(7), > D5 varchar(5), > E2 varchar(11), > E5 varchar(30), > OSSERVAZIONI1 text, > F4 varchar(2), > F5 varchar(2), > F6 varchar(4), > G5 varchar(2), > G6 varchar(2), > G7 varchar(2), > G8 varchar(2), > G9 varchar(2), > G10 varchar(2), > G11 varchar(2), > G12 varchar(2), > G13 varchar(2), > G14 varchar(2), > H1 varchar(3), > H2 varchar(3), > H3 varchar(3), > H4 varchar(3), > H6 varchar(3), > H7 varchar(11), > OSSERVAZIONI2 text, > I1 varchar(2), > I2 varchar(2), > I3 varchar(3), > M1 varchar(20), > M2 varchar(2), > M3 varchar(2), > M8 varchar(50), > M9 varchar(50), > M10 varchar(50), > M11 varchar(50), > OSSERVAZIONI3 text > )\g > > INSERT INTO DATI > VALUES ( > 'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO', > 'VIA B. CLESIO 3', > 'TRENTO CENTRO', > '0461-230482', > 'BIBLIOTECA', > '09.00 - 12.00 14.30 - 16.30', > 'LUNEDI\'', > 'NO', > 'SI', > '18', > '13', > 'ESTERNI', > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > 'MANUALE', > 'NO', > 'SI', > '206', > 'INTERNO', > NULL, > '110', > 'ORIZZONTALE', > '160', > 'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.', > 'NO', > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > 'SI', > 'NO', > '110', > '91', > 'NO', > 'SI', > 'SI', > 'SI', > '1.82', > '1.86', > 'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g > > INSERT INTO DATI > VALUES ( > 'SALA DI PUBBLICA LETTURA', > 'VIA SALE\' 1', > 'POVO', > '0461-810241', > 'BIBLIOTECA', > 'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30', > 'SABATO', > 'SI', > 'NO', > ....................................................... > > and the output-error of psql is: > -- Exported from MS Access to mSQL > -- (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net > > > CREATE TABLE DATI > ( > ESERCIZIO varchar(100), > VIA_E_NUMERO varchar(61), > LOCALITA' varchar(50), > TELEFONO varchar(30), > TIPOLOGIA varchar(50), > ORARIO_DI_APERTURA varchar(100), > RIPOSO varchar(51), > A1 varchar(2), > B1 varchar(2), > B2 varchar(10), > B3 varchar(3), > B4 varchar(30), > B5 varchar(5), > B6 varchar(2), > B7 varchar(4), > B8 varchar(2), > B9 varchar(15), > B10 varchar(4), > B11 varchar(2), > B12 varchar(3), > B13 varchar(2), > B14 varchar(20), > B15 varchar(3), > C1 varchar(10), > C2 varchar(2), > C3 varchar(2), > C6 varchar(10), > C7 varchar(15), > D2 varchar(7), > D5 varchar(5), > E2 varchar(11), > E5 varchar(30), > OSSERVAZIONI1 text, > F4 varchar(2), > F5 varchar(2), > F6 varchar(4), > G5 varchar(2), > G6 varchar(2), > G7 varchar(2), > G8 varchar(2), > G9 varchar(2), > G10 varchar(2), > G11 varchar(2), > G12 varchar(2), > G13 varchar(2), > G14 varchar(2), > H1 varchar(3), > H2 varchar(3), > H3 varchar(3), > H4 varchar(3), > H6 varchar(3), > H7 varchar(11), > OSSERVAZIONI2 text, > I1 varchar(2), > I2 varchar(2), > I3 varchar(3), > M1 varchar(20), > M2 varchar(2), > M3 varchar(2), > M8 varchar(50), > M9 varchar(50), > M10 varchar(50), > M11 varchar(50), > OSSERVAZIONI3 text > )\g > > INSERT INTO DATI > VALUES ( > 'MUSEO STORICO D/RISORGIMENTO C/O CASTELLO BUONCONSIGLIO', > 'VIA B. CLESIO 3', > 'TRENTO CENTRO', > '0461-230482', > 'BIBLIOTECA', > '09.00 - 12.00 14.30 - 16.30', > 'LUNEDI\'', > 'NO', > 'SI', > '18', > '13', > 'ESTERNI', > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > 'MANUALE', > 'NO', > 'SI', > '206', > 'INTERNO', > NULL, > '110', > 'ORIZZONTALE', > '160', > 'LE PORTE IN ORARIO DI VISITA SONO SEMPRE APERTE.', > 'NO', > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > NULL, > 'SI', > 'NO', > '110', > '91', > 'NO', > 'SI', > 'SI', > 'SI', > '1.82', > '1.86', > 'I SERVIZI IGIENICI SONO ESTERNI ALLA SALA DEL MUSEO.' )\g > > ERROR: parser: parse error at or near "'" > > INSERT INTO DATI > VALUES ( > 'SALA DI PUBBLICA LETTURA', > 'VIA SALE\' 1', > 'POVO', > '0461-810241', > 'BIBLIOTECA', > 'LU. GIO. 10.00 - 12.00 14.30 - 18.30 MA. ME. VE. 14.30 - 18.30', > 'SABATO', > 'SI', > ......................................... > and in the standard optput there was nothing! > > Why? > > Thanks > > Ludo > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Dave Cramer writes: > test=# create table custbase (phonenumber int8, svctype char, svcchoice > char, billtype char, ctype char); > CREATE > test=# create index custbaseidx on custbase (phonenumber); > CREATE > test=# \d custbase > Table "custbase" > Attribute | Type | Modifier > -------------+--------------+---------- > phonenumber | bigint | > svctype | character(1) | > svcchoice | character(1) | > billtype | character(1) | > ctype | character(1) | > Index: custbaseidx > > test=# explain select * from custbase where phonenumber=5199400858; > NOTICE: QUERY PLAN: > > Seq Scan on custbase (cost=0.00..25.00 rows=10 width=56) > > EXPLAIN > test=# explain select * from custbase where phonenumber='5199400858'; > NOTICE: QUERY PLAN: > > Index Scan using custbaseidx on custbase (cost=0.00..8.14 rows=10 width=56) This is a long-standing problem, and you picked the right workaround. Btw., I would think that bigint is not the optimal choice for phone numbers. I would use a character type. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
When you aren't putting the quotes, the type of the numeric constant gets int4 which won't match bigint so it doesn't realize it can use the index. You can explicitly type it with ::bigint which should make it use the index as well (current workaround until numeric types get handled in a more happy way). For more details see archives. On Tue, 22 May 2001, Dave Cramer wrote: > > Hi All, > > Below is a simple table with an index on phonenumber, if I do a select on > phonenumber without quotes then it does a sequence scan?? If I do put > quotes, then it does use the index scan??? Anyone know what's going on? > > The version of postgres is 7.1 > > Regards, > > Dave > > test=# create table custbase (phonenumber int8, svctype char, svcchoice > char, billtype char, ctype char); > CREATE > test=# create index custbaseidx on custbase (phonenumber); > CREATE > test=# \d custbase > Table "custbase" > Attribute | Type | Modifier > -------------+--------------+---------- > phonenumber | bigint | > svctype | character(1) | > svcchoice | character(1) | > billtype | character(1) | > ctype | character(1) | > Index: custbaseidx