LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
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!

Notices


Reply
  Search this Thread
Old 05-26-2009, 10:38 PM   #1
justemail
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
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,254

Rep: Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328Reputation: 2328
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

Code:
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
justemail
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.

Jenn
 
Old 05-27-2009, 10:25 AM   #4
justemail
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.

Jennifer
 
  


Reply


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


All times are GMT -5. The time now is 11:33 PM.

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