Connecting to PostgreSQL with PHP

Let’s look at how we can use PostgreSQL with PHP.


I will not talk about why you should use PostgreSQL over Mysql, and this article is not about how you can install PostgreSQL on your system.


How to connect to PostgreSQL

There are two ways that you can connect to PostgreSQL with PHP;

  1. Pgsql Extension
  2. PDO Pgsql Driver

We’ll use PDO Driver which is simpler than Pgsql Extension.


Installing PDO Driver

On Ubuntu you can install with one line command.

sudo pecl install pdo_pgsql

This will install PDO pgsql driver.

On Windows you just need to edit php.ini

;extension=php_pgsql.dll

find this line and remove “;”.

If you are using WAMP server, after enabling the POD PostgreSQL driver, it is still not working and you got the following error message:

could not find driver

You can put the following line:

LoadFile "c:/wamp/bin/php/php[yourphpversion]/libpq.dll"

At the end of the httpd.conf file in the C:\wamp\bin\apache\apache2.2.22\conf folder.


Connection to PostgreSQL

Once the driver is installed, you can connect as following;

$pdo = new PDO("pgsql:host=localhost;dbname=yourdbname", "root", "yourpassword");

if that does not work try that way;

$pdo = new PDO("pgsql:host=localhost;dbname=yourdbname;user=root;password=yourpassword");

Connect to Docker PostgreSQL Container

Unless you use Docker you can skip this section.

All you have to do is put your docker name instead of localhost

For example, this is my docker file;

postgres:
image: postgres:9.6-alpine
container_name: ordm-postgres
working_dir: /application
ports:
- "5432:5432"
volumes:
- .:/application
environment:
- POSTGRES_USER=root
- POSTGRES_PASSWORD=mypassword
- POSTGRES_DB=docker

and this is how i connect to PostgreSQL;

$pdo = new PDO("pgsql:host=ordm-postgres;dbname=docker", "root", "my password");

Be Careful

When you use PostgreSQL instead of Mysql and you are a beginner of Pgsql you might have issue with LIMIT statement.


You probably used LIMIT statements like below when using Mysql.

LIMIT 0,10

but PostgreSQL does not support this statement; You have to use like that;

LIMIT 10 OFFSET 0

you better use this way always because this is a ANSI statement which is supported almost every sql server.