How to connect a PostgreSQL database server using PHP PDO?

Before connecting with the PostgreSQL database using PHP PDO, make sure PHP PDO PostgreSQL driver enabled.

To check if the PDO PostgreSQL driver is enabled, open the php.ini file and check if the following line is un-commented. If it is not, you can remove the semicolon ( ;) in front of the entry.

extension=php_pdo_pgsql.dll

PostgreSQL data source name

The data source name or DSN passes on the database parameters that permit you to connect with the database. PDO characterizes distinctive DSN for different databases. The data source name of the PostgreSQL is made out of the following parameters:

1. DNS prefix: pgsql:

2. host: the database server’s hostname where the PostgreSQL database locates.
   

3. port: default port is 5432.
   

4. dbname: database name.
   

5. user: The name of the user that connects to the database dbname.
   

6. password: The password of the user name.
   

Notice that PDO ignores the username and password in the PDO constructor if you put them in the data source name (DSN).

Connecting to PostgreSQL

The following snippet allows you to connect to the database in the PostgreSQL database server:

<?php
 
$host='localhost';
$db = 'database';
$username = 'user';
$password = 'password';
 
$dsn = "pgsql:host=$host;port=5432;dbname=$db;user=$username;password=$password";
 
try{
 // create a PostgreSQL database connection
 $conn = new PDO($dsn);
 
 // display a message if connected to the PostgreSQL successfully
 if($conn){
    echo "Connected to the <strong>$db</strong> database successfully!";
 }
} catch (PDOException $e){
 // report error message
 echo $e->getMessage();
}