Обсуждение: Read data from text file

What is the command to create a  table from a pre-written  description
text file?

In mysql it is:

%mysql sample_db < create_xxxx.sql

In postgresql, there are schema and I want to create the DB in a
particulat schema!

Thanks.

Just E. Mail wrote:
> What is the command to create a  table from a pre-written description
> text file?
>
> In mysql it is:
>
> %mysql sample_db < create_xxxx.sql
>
> In postgresql, there are schema and I want to create the DB in a
> particulat schema!



When I have multiple schemas in one database; I generally have a user
that "owns" each schema, as in:

      CREATE USER kevin;
      CREATE SCHEMA kevin AUTHORIZATION kevin;

Then I process the sql commands with:

% psql db_kevin kevin < create-kevin.sql

Frank Bax wrote:
>
> When I have multiple schemas in one database; I generally have a user
> that "owns" each schema, as in:
>
>      CREATE USER kevin;
>      CREATE SCHEMA kevin AUTHORIZATION kevin;
>
> Then I process the sql commands with:
>
> % psql db_kevin kevin < create-kevin.sql
Hello:

I was going to post this question first thing this AM. You read my mind
and already answered. BIG THANKS.

Jennifer

Frank Bax wrote:
>
> Then I process the sql commands with:
>
> % psql db_kevin kevin < create-kevin.sql

I am making progress but still not there.

Using pgADMIN, I created database name 'my_db'. Under 'my_db', I created
a 'SCHEMA' named 'test' (so under 'my_db' there are two SCHEMAs ; public
& test). I use the command to read a schema text file:

% psql   my_db   radius  <  schema.sql

and each time the tables are created in the 'public' schema. How to
direct 'psql' command to insert the tables into the 'text' (or any other
SCHEMA I so desire)?

Just E. Mail wrote:
> I am making progress but still not there.
>
> Using pgADMIN, I created database name 'my_db'. Under 'my_db', I
> created a 'SCHEMA' named 'test' (so under 'my_db' there are two
> SCHEMAs ; public & test). I use the command to read a schema text file:
>
> % psql   my_db   radius  <  schema.sql
>
> and each time the tables are created in the 'public' schema. How to
> direct 'psql' command to insert the tables into the 'text' (or any
> other SCHEMA I so desire)?
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

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