Re: importing data automatically

Kurt,

> I am running PostgreSQL 7.3.2 on RedHat 8. I have many large census tables
> I need to import. I access the Linux box from my Windows 2000 machine via
> Cygwin and can access PostgreSQL similarly via EMS PostgreSQL Manager. I
> understand that a table must be created with the correct fields before
> INSERT or COPY can be used, or before the data import wizard in EMS can be
> used. My problem is that I have some very large tables with hundreds of
> fields to import. Is there a way to get PostgeSQL to honor the database
> schema from an MS Access database and create the appropriate fields on
> import automagically?

Sure. Use pgODBC to connect to the PostgreSQL database from MS Access. Use
that connection to "export" the table design from MS Access to PostgreSQL
(but not the data, just the design!).

Log into PostgreSQL and adjust the data types and of the fields, becuase the
first step will not have worked perfectly.

Create COPY files and use those to load the new tables.

Create your indexes and FKs *after* import.

--
-Josh Berkus
Aglio Database Solutions
San Francisco