Re: msaccess to postgre - Mailing list pgsql-novice

Peter,

Usually the search paths by default are set as  $user, public.  Postgres
puts tables in the first schema in search path (if it exists) and then
tries the next if the previous does not exist,  if no schema
qualification. 

So to force Access to put tables in a certain schema, one way to do it
is to create an account with the same name as the schema - e.g. create
an account called accounts and log in as that and export all your
accounts tables etc..

I'm really not quite sure why your approach puts it in the public always
unless your search path settings were not saved (did you restart/reload
config of your postgresql server after setting search paths?)

As far as moving tables from public to another schema.  There is no
intuitive way I know of doing this.
Check out the link below.  From my simple tests, this stored function
seems to work fairly well for moving tables and their indexes from one
schema to another.  PostgreSQL by the way also seems to automatically
correct the views and inheritance affected to.

http://archives.postgresql.org/pgsql-sql/2003-12/msg00081.php

Hope that helps,
Regina


-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Peter Jackson
Sent: Tuesday, January 15, 2008 8:13 AM
To: pgsql
Subject: [NOVICE] msaccess to postgre

OK I'm currently trying to get my head around postgre, sql in general 
and php. So if this question seems easy dont mind me.

  What I wish to do is move several ms access dbases to postgre using 
ODBC connection. I have managed to do this but where I have hit a 
brickwall is trying to get it to add the tables to a different schema.

I have a database called general in that database there are 4 schemas.
  accounts, production,maintenance and public.
I have set the search path to production, accounts, maintenance, public.
But the ODBC connection still saves it to the public schema. (I want it 
saved in this instance to production schema.) Yes the user I am 
connecting as has create privileges in production.

  Anyone got any suggestions or easier (non-commercial) way of doing it?

Failing that is it possible to move/copy a table from one schema to
another?

thanks in advance


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.