Download your favorite Linux distribution at LQ ISO.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 05-26-2009, 10:38 PM   #1
LQ Newbie
Registered: Apr 2009
Posts: 29

Rep: Reputation: 15
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.
Old 05-27-2009, 02:33 AM   #2
LQ Guru
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,417

Rep: Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397
Well, looking at &, I'd guess something like

psql -d test -U radius -f mytestfile.sql
assuming no passwd reqd. I suspect if you don't use the right switches, it defaults to the public schema...
Old 05-27-2009, 06:29 AM   #3
LQ Newbie
Registered: Apr 2009
Posts: 29

Original Poster
Rep: Reputation: 15
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.

Old 05-27-2009, 10:25 AM   #4
LQ Newbie
Registered: Apr 2009
Posts: 29

Original Poster
Rep: Reputation: 15
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.



Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: OSCON 2006: TimeTravel Tables in PostgreSQL LXer Syndicated Linux News 0 08-06-2006 11:54 AM
Newbie C Question from K&R noir911 Programming 5 03-04-2006 06:17 PM
Populating tables in Postgresql rickh Linux - Software 11 03-02-2005 11:34 PM
Newbie ?, IP tables & Security jmr0311 Linux - Security 6 07-17-2004 09:39 AM
postgresql -odbc & postgresql-jdbc installation kjsubbu Linux - Software 0 06-19-2003 03:50 AM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 12:20 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration