CODEWITHSUNDEEP
phppostgresqlpdo
Channaveer Hakari
Channaveer Hakari

Reputation: 2927

How to connect to a Postgres database without specifying a database name in PDO?

I am using the following connection to connect to Postgres SQL without Database as I need to fetch all the database names later for configuration

try{
    $this->connection = new \PDO($this->database.":host=".$this->host,$this->user,$this->password);
    $this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    return $this->connection;
}catch(\PDOException $e){
    echo $e->getMessage();
}

But I am getting the following error 

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

The following are the values that are set in the properties:

$this->database = pgsql
$this->host = localhost
$this->user = admin
$this->password = admin

Can anyone please help me out how to connect to Postgres SQL without any database selection with PHP PDO

Upvotes: 6

Views: 5459

Answers (7)

William Prigol Lopes
William Prigol Lopes

Reputation: 1889

You always need to connect on a database.

If you not inform, the connector tries to connect in a database with the same name of the user that you tried to connect, so, if you try to connect with "admin" user, the postgresql tries to connect on "admin" database.

If postgresql can't find the "admin" database, the connector returns the error mentioned on answer.

All postgresql databases have the "internal tables", wich have the prefix "pg_".

So, if you want a list of databases in postgresql, you can try to run the following select in any database:

warehouse2=# select datname from pg_database;
  datname   
------------
 postgres
 template1
 template0
 warehouse2
(4 rows)

In this example, I listed all databases on my cluster, containing 4 databases.

Upvotes: 1

harmic
harmic

Reputation: 30587

Postgresql does not have a possibility to connect to the database server without specifying to an existing database.

The reason you are getting this error:

SQLSTATE[08006] [7] FATAL: database "admin" does not exist

is that if you do not specify a database name, it will default to trying to connect to a database with the same name as the user you have specified (see description of connection parameter dbname).

When postgresql is first initialized, three default databases are created:

  • postgres - intended as a general purpose database for the default administrator user postgres to access
  • template0 - Master template - should not be changed after initialization
  • template1 - Default template - used to create all new databases from

See Template Databases for a full description of these.

If you want to get a list of the available databases, you will need to connect to one of the above databases.

I would normally use template1 for this, because in theory the postgres database might have been dropped, and it is dangerous to connect to the template0 database in case you accidentally change it. Since template1 is used as the default template when creating databases, it would most likely exist.

Also note that whatever user you are connecting with needs to have permission to connect to the given database. All three of these databases are owned by the postgres user, which means if you do not connect using this user, you will have to make sure the user you are using has permission to access the database.

Upvotes: 1

Felipe Valdes
Felipe Valdes

Reputation: 2217

In addition to the current excellent answers, also, Consider creating a db for each unix user (or at least for the "admin" user), so the "psql" command works without parameters and you can conveniently use it for interactive use.

Upvotes: 0

Yannoff
Yannoff

Reputation: 374

For what I've understood from th error message, it seems your DSN is wrong (admin, which is your username and/or password is interpreted as the database name)...

Upvotes: 0

Steve Chambers
Steve Chambers

Reputation: 39424

This answer explains it well:

https://stackoverflow.com/questions/2370525#2411860

You'll need to explicitly point to the one database that is always guaranteed to exist as described above, namely "postgres": $this->connection = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

...or if preferred, set $this->dbname = postgres and then $this->connection = new PDO($this->database.":host=".$this->host.";dbname=".$this->dbname,$this->user,$this->password);

Upvotes: 0

Michał Zaborowski
Michał Zaborowski

Reputation: 4387

There are tree options:

  • postgres
  • template0
  • template1

Please, be aware that connection to the database requires permission. Here is explanation what that templates are. You can look for postgresql default database. Which is quite similar topic.

Upvotes: 2

Hervé Piedvache
Hervé Piedvache

Reputation: 798

As you always have a default database called postgres on PostgreSQL you can By default make a connection to it like this :

$dbh = new PDO($this->database.":host=".$this->host.";dbname=postgres",$this->user,$this->password);

Then you will make another one after to the good one.

Upvotes: 0

Related Questions