Newbie question about PostgreSQL, Schemas & Tables!
 

I am hard time figuring out this problem, so I am hoping somebody on this FORUM can help.

I am setting up a server with CentOS 5.3, PostgreSQL 8.3.7, freeRADIUS & Postfix. All data is supposed to be in the PostgreSQL tables.

For the time being, I have created a text file 'mytestfile.sql' to create tables.

This just a TEST!!!!

On the server, I have created a database name 'my_db'. Under 'my_db', I created a 'SCHEMA' named 'test' (so under 'my_db' there are two SCHEMAs ; public, which is automatically created & test).

Now I am trying to read-in the text file 'mytestfile.sql' using this command:

% psql my_db radius < mytestfile.sql

(radius is the owner of 'test')

Problem is that when I execute the above command, it reads the 'mytestfile.sql' text file fine except it creates the tables in the 'public' schema instead of 'test' schema.. How to change the 'psql' command to insert the tables into the 'text' (or any other SCHEMA I so desire)?

I have posted this question on PG Forum with no response. Hope somebody has an answer here.

Jennifer K.

PS: If you want to know why am I doing this? Well I plan to have three SCHEMAs, each having their own tables. More if you need to know.

Well, looking at http://www.postgresql.org/files/docu...l/node143.html & http://www.postgresql.org/docs/8.1/static/app-psql.html, I'd guess something like

assuming no passwd reqd. I suspect if you don't use the right switches, it defaults to the public schema...

I believe I figured out how to solve this problem:

1. No need to create my own database 'my_db',
2. Create a new SCHEMA in the 'postgre' database,
3. set search path to my SCHEMA,
4. Load/insert data from a text file.

That's it. I have not tried it yet but the research I did shows that it will work.

Jenn

For those who are following or may read this post in the future:

My plan listed above did not work. When I tried to change the DEFAULT SCHEMA using SET command, the data will still be inserted into the 'public' schema.

Finally I figured out that the DEFAULT setting is set in 'postgresql.conf' file; therefore I hanged the following line:

search_path = ‘”$user”,public” ; “#” & Change to
search_path = ‘”$user”,fr_schema”
and then:

%> psql -f <fileto read> <Database> <Owner>

%> psql -f schemq.sql postgres radius
It worked. Hope it help others.

Jennifer