PostgreSQL: Create schema in specific database [NOT psql]

1
votes

Im trying to create a SQL file to import my database/schema/tables with PHP.

If I use the PgAdmin, it inserts a meta-command \connect after the CREATE TABLE and this generates an error if I run the SQL with pg_query in PHP.

After read the following questions: PostgreSQL: Create schema in specific database and Postgres Creating Schema in a specific database

I ask: How I can create a new schema, inside specific database, with SQL commands (NOT psql commands)? The specific database is not the default named 'postgres' database, but another created by me.

1
phpsqlpostgresql

1 Answers

3
votes

The short answer is if you are connected to another database you can't with pure SQL.

Due to the fact that there is no use database; in Postgres like in MySQL you have to open a connection to the specific database you want to create the schema in (e.g. with pg_connect in PHP).

The schema itself can be created with the CREATE SCHEMA... sql command as you might know.

Perhaps working with pg_dump and pg_restore is an option for you. I would also have a look at the sources of phpPgAdmin.

I hope that helped a bit.

*Jost