postgresql unable to create table in schema

Question

I'm creating schema/db/... with this script:

CREATE ROLE testuser LOGIN PASSWORD 'password';
CREATE TABLESPACE testtablespace OWNER testuser LOCATION '/pgdata/pg94/testtablespace';
CREATE SCHEMA testschema;
ALTER SCHEMA testschema OWNER TO testuser;
CREATE DATABASE testdb WITH TABLESPACE testtablespace ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0 OWNER testuser;
ALTER DATABASE testdb SET search_path TO testschema, public;
ALTER ROLE testuser SET search_path TO testschema, public;
GRANT ALL ON DATABASE testdb TO testuser;
GRANT ALL ON SCHEMA testschema TO testuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA testschema TO testuser;
ALTER ROLE testuser SET default_tablespace = testtablespace;

when I log in with command psql -U testuser testdb and execute comand select schema_name from information_schema.schemata ;. What is worse I can't create table within this schema

testdb=> create table testschema.test (test varchar(10));
ERROR:  schema "testschema" does not exist

please what should I configure to view schema and also create tables in int?

klin · Accepted Answer · 2015-12-01 16:44:32Z

2

CREATE SCHEMA always applies to current database. You have created testschema in current database (probably postgres), not in testdb. You should connect to testdb and then create new schemas.

answered Dec 1, 2015 at 16:44

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bilak Over a year ago

Is there way how to create schema for some db without connecting to that db? Something like create schema testdb.testschema ?

klin Over a year ago

I'm affraid it's impossible. You can check the syntax.

Collectives™ on Stack Overflow

postgresql unable to create table in schema

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related