re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
От | Gilley, Charles H. |
---|---|
Тема | re: [INTERFACES] Postgresql vs. Access97, TransferDatabase |
Дата | |
Msg-id | 18D7589E9C44D21199AC00805F31B04708A0BA@atlanta_nt2.atlanta.glenayre.com обсуждение исходный текст |
Ответ на | [INTERFACES] Postgresql vs. Access97, TransferDatabase (Patricia Rodriguez-Tome <tome@ebi.ac.uk>) |
Ответы |
Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
|
Список | pgsql-interfaces |
Okay, to provide some helpful feedback to others.... I readily admit that I am still a novice at this, so feel free to chime in and correct any misunderstandings that I may have. First, I've been trying to transfer a large dataset from access to postgres using a variety of methods: 0) copy command - never could get this to work. The copy command requires postgres superuser access for backend processing, which I did not have. There is an alleged \copy command that I could not work either - it kept processing it as a connect request. 1) Cut and paste from access table to linked table: This is very slow and tedious, as one writer indicates. Further, it is quite sensitive to data type and field name issues. Just because Access allows you to do something doesn't mean postgres or odbc will. 2) Macro to TransferDatabase - if your table will map PERFECTLY 1:1 to the postgres table, this may work. 3) Export table - I believe this is the same as cutting/pasting except that it attempts to create the table on the backend as well. I did not have much success with this either, due to the hidden fields associated with replication. 4) SQL - this is what I finally used to update the postgres database. Once I had the tables defined in an acceptable manner, I created an update query: INSERT INTO tblproducts1 SELECT productid AS productid, ... BarCode10 AS BarCode10 FROM tblproducts The simplest form of this insert is: INSERT INTO tblproducts1 SELECT * FROM tblproducts; but this only works if your tables map PERFECTLY. using the field AS field format keeps Access and the ODBC from thinking too much. I also gave up trying to use the drag/drop gui in the query builder. 5) See item 0. Because I could not get the privs or otherwise of the copy command to operate properly, I considered creating a utility to generate a series of insert into commands, parsing a comma delimited file as a data source. I believe I will still craft this (since I have more data to load). This would allow me to zip a text file up and ftp it to my server location. If I get this working, I'll be happy to provide it to anyone who wants it (maybe post the source on a postgres site?). Beware: comma delimited files produced by Access simply will not be imported by postgres. For example, a null text field is '' (two single quotes). Access does not generate this. Suggestions: + Read the postodbc faq. one of my problems was a missing type - I just needed to create it. + Watch out for databases that have been replicated. I have yet to locate the extra fields and remove them. These will give you a large amount of grief. + Watch out for 'bad' characters in field names. + Use the psqlodbc.log file judiciously.
В списке pgsql-interfaces по дате отправления:
Предыдущее
От: Sbragion DenisДата:
Сообщение: Re: [INTERFACES] Postgres mentioned in Information Week