0

How can I switch to a different database in POSTGRES via PHP?

I mean a way similar to

mysqli_select_db( $db_conn, $db_name )

on any other workaround, but using the same connection.

5
  • You can't as in MySQL database is a synonym for schema, whereas in Postgres it is actually a different database. You will need to either: 1) Disconnect and reconnect to new database or 2) Or use postgres-fdw to link(it creates a behind the scenes connection) the databases. Commented Aug 15 at 17:29
  • Thank you! This is what I finally guessed to do in a comment I added below. Commented Aug 15 at 20:26
  • Did you look at option 2)? Then you would not have to disconnect/reconnect, the link is taken care of by the Postgres server. Commented Aug 15 at 20:46
  • It is still not clear what you want to switch, a schema or a database. Commented Aug 16 at 6:43
  • 1
    Topically related insights: "use database_name" command in PostgreSQL Commented Aug 16 at 7:39

3 Answers 3

2

The concept is different:

Each MySQL server has ONE SINGLE database, and inside this SINGLE database, it might have many database schemas called "database".

Each PostgreSQL can have many databases, and each database might have many schemas.

When you connect to a database (the real database, not a schema), you can change the schema (or "database" in MySQL) that is used. And you can see what schema you're currently using.

SELECT current_database()
     , current_schema();

https://www.postgresql.org/docs/current/sql-createschema.html https://dev.mysql.com/doc/refman/9.4/en/create-database.html

From the MySQL documentation:

CREATE SCHEMA is a synonym for CREATE DATABASE.

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

3 Comments

Thanks a lot for your insight ! It seems that one has to close the current connection and open to the new database.
@SandroRosa that's correct: In MySQL, there is only one database, that's the one you (can) use. And you can always switch to a different schema if you want to. In PostgreSQL, you first have to select which database to use, and then you use the schema you'd like to use. If you really want to use PostgreSQL like MySQL, create a single database and different schemas. But most likely you'll drop this idea within a week...
Thanks for your valuable advice ! I'll keep on approaching Postgres in the standard way.
2

There is no direct equivalent (obviously you can disconnect and reconnect to a different DB, but not switch within the same connection).

I don't know if mysql has schemas (namespaces) in-database yet, but if not traditionally schemas were considered the closest equivalent. You can control permissions at a schema level, and have cross-schema joins etc within a single PostgreSQL database.

2 Comments

Thanks! So you mean running in sequence pg_close and again pg_connect ?
FWIW, in MySQL schema and database are synonyms. A MySQL instance effectively has one "database" per instance, and you can switch schemas during a session, or even reference tables from other schemas in a given query. Both schema and database refer to this.
2

If you are using psql, then it's

\c yourdb

In PHP you can use pg_connect to connect to a PostgresSQL database. You can pass the connection string and it will return a connection object. If you already had a connection to another database and you no longer need it, then it is recommended to use pg_close to close that connection.

EDIT

You can implement a wrapper for this purpose which will encapsulate the close and the connect and whose name would look alike somewhat the name you had for mysqli:

pg_select_db(string $connection_string, ?PgSql\Connection $connection = null, int $flags = 0): PgSql\Connection|false {
    pg_close($connection);
    return pg_connect($connection_string, $flags);
}

and then you have a single method to call.

5 Comments

I was not actually clear in my question. I meant using PHP.
@SandroRosa sorry about that. Edited the answer.
@SandroRosa yet, for some reason, you didn't add this essential tag to your question
Thanks, this is what I figured out at last: close the current conneccion and reconnect to another database. Surprisingly, this shortcut has been not implemented into the standard library.
@SandroRosa edited the answer with a wrapper function that would fulfill your goal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.