Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tables not created in proper schema

Tables not created in proper schema

From
"Ashish Karalkar"
Date:
Hello All,
I am trying to create databse with script.
I run this script from root prompt with command
 
 
$ su - postgres -c 'path to script.sql'
 
 
In the script I follow following steps
 
 
1) create user xyz
2) create database xyz -O xyz
3) create schema xyz
4) {PG_PATH}/psql -d xyz -U xyz -f /usr/local/pgsql/QS/QS_100_2_Create_Table.sql -q -1
 
 
But still the tables are created in the public schema and not in xyz schema
 
 
Postgresql.conf entry:
serach path =' "$user",public'
 
tried with setting :
search path = ' xyz,"$user",public'
Same result
 
OR
search path = ' "$user",xyz,public'
Same result
 
OR
search path = ' "$user",xyz'
Error:no schema has been selected to create in
 
 
Please suggest me the corrective action.
 
Thanks In advance
 
Ashish...
 
 

Re: Tables not created in proper schema

From
"A. Kretschmer"
Date:
am  Mon, dem 02.07.2007, um 11:13:54 +0530 mailte Ashish Karalkar folgendes:
> Hello All,
> I am trying to create databse with script.
> I run this script from root prompt with command
>
>
> $ su - postgres -c 'path to script.sql'
>
>
> In the script I follow following steps
>
>
> 1) create user xyz
> 2) create database xyz -O xyz
> 3) create schema xyz

ALTER USER xyz SET SEARCH_PATH = ' ... ';

or set the search_path at the beginning of your sql-file.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Tables not created in proper schema

From
"Ashish Karalkar"
Date:
Thanks for your replay.

I think the problem is with schema not being recognised.

following are the line from sql script:(This script is run as a postgres
user with password authntication from .pgpass file)



${PG_PATH}/createuser qsweb -S -d -R -l -P -E -q -U postgres

${PG_PATH}/createdb -E UTF8 -O qsweb -U postgres qsweb

${PG_PATH}/createlang -U postgres -d qsweb plpgsql

${PG_PATH}/psql -d qsweb --command "CREATE SCHEMA qsweb"

${PG_PATH}/psql -d qsweb --command "ALTER SCHEMA qsweb OWNER TO qsweb"

${PG_PATH}/psql --command "ALTER USER qsweb SET SEARCH_PATH='qsweb'"



The output is right till  alter schema but then while setting the search
path it says NOTICE:schema "qsweb" does not exist.



can you suggest what is going wrong

Thanks in advance

Ashish...















----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, July 02, 2007 2:28 PM
Subject: Re: [GENERAL] Tables not created in proper schema


> am  Mon, dem 02.07.2007, um 11:13:54 +0530 mailte Ashish Karalkar
> folgendes:
>> Hello All,
>> I am trying to create databse with script.
>> I run this script from root prompt with command
>>
>>
>> $ su - postgres -c 'path to script.sql'
>>
>>
>> In the script I follow following steps
>>
>>
>> 1) create user xyz
>> 2) create database xyz -O xyz
>> 3) create schema xyz
>
> ALTER USER xyz SET SEARCH_PATH = ' ... ';
>
> or set the search_path at the beginning of your sql-file.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

Re: Tables not created in proper schema

From
"Albe Laurenz"
Date:
Ashish Karalkar wrote:
> ${PG_PATH}/psql -d qsweb --command "CREATE SCHEMA qsweb"
>
> ${PG_PATH}/psql -d qsweb --command "ALTER SCHEMA qsweb OWNER TO qsweb"
>
> ${PG_PATH}/psql --command "ALTER USER qsweb SET SEARCH_PATH='qsweb'"
>
>
> The output is right till  alter schema but then while setting
> the search path it says NOTICE:schema "qsweb" does not exist.

Hardly surprising, as you forgot '-d qsweb' in the last command.

Yours,
Laurenz Albe