{"id":136,"date":"2021-03-08T00:28:08","date_gmt":"2021-03-08T00:28:08","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=136"},"modified":"2025-04-09T03:25:43","modified_gmt":"2025-04-09T03:25:43","slug":"pdo-connecting-to-postgresql","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-postgresql\/","title":{"rendered":"Connecting to PostgreSQL"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to connect to a PostgreSQL database server using PHP PDO.<\/p>\n\n\n\n<p class=\"note\">If you want to learn about PostgreSQL or review it as a refresher, follow this <a href=\"https:\/\/www.pgtutorial.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL tutorial<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='prerequisites'>Prerequisites <a href=\"#prerequisites\" class=\"anchor\" id=\"prerequisites\" title=\"Anchor for Prerequisites\">#<\/a><\/h2>\n\n\n\n<p>To make a connection to the PostgreSQL database server using PHP PDO, you need to have:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A PostgreSQL database server, a database, and an account with a username and password that can access the database.<\/li>\n\n\n\n<li>PHP PDO PostgreSQL driver enabled in the <code>php.ini<\/code> file.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id='postgresql-database-parameters'>PostgreSQL database parameters <a href=\"#postgresql-database-parameters\" class=\"anchor\" id=\"postgresql-database-parameters\" title=\"Anchor for PostgreSQL database parameters\">#<\/a><\/h3>\n\n\n\n<p>Suppose you have the following PostgreSQL database parameters:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A PostgreSQL database server on the <code>localhost<\/code> .<\/li>\n\n\n\n<li>The <code>inventory<\/code> sample database.<\/li>\n\n\n\n<li>The account with the user <code>postgres<\/code> and password <code>postgres<\/code> that can access the <code>inventory <\/code>database on the local database server<\/li>\n<\/ul>\n\n\n\n<p>The following creates a new database configuration file named <code>config.php<\/code> that stores the PostgreSQL database parameters:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n$host= <span class=\"hljs-string\">'localhost'<\/span>;\n$db = <span class=\"hljs-string\">'inventory'<\/span>;\n$user = <span class=\"hljs-string\">'postgres'<\/span>;\n$password = <span class=\"hljs-string\">''<\/span>; <span class=\"hljs-comment\">\/\/ change to your actual password<\/span><\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To use these parameters, you include the <code>config.php<\/code> file in the script that connects to the PostgreSQL using the <code><a href=\"https:\/\/phptutorial.net\/php-tutorial\/php-require\/\">require<\/a><\/code> construct.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='enabling-the-postgresql-driver'>Enabling the PostgreSQL driver <a href=\"#enabling-the-postgresql-driver\" class=\"anchor\" id=\"enabling-the-postgresql-driver\" title=\"Anchor for Enabling the PostgreSQL driver\">#<\/a><\/h3>\n\n\n\n<p>The <code>PDO_PGSQL<\/code> is a driver that implements the PDO interface. It allows you to access PostgreSQL databases from PHP.<\/p>\n\n\n\n<p>To check if the <code>PDO_PGSQL<\/code> driver is enabled, you open the <code>php.ini<\/code> file. Typically, the <code>php.ini<\/code> file is located under the <code>php<\/code> directory. For example, if you use XAMPP on Windows, you can find the <code>php.ini<\/code> file under the <code>C:\\xampp\\php<\/code> directory.<\/p>\n\n\n\n<p>in the <code>php.ini<\/code> file, you can find the following line:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">;extension=php_pdo_pgsql.dll<\/code><\/span><\/pre>\n\n\n<p>If you see the comma(<code>;<\/code>) placed at the beginning of the line, it means that the line is commented and the database driver is not enabled.<\/p>\n\n\n\n<p>To enable the driver, you need to uncomment the line by removing the comma (<code>;<\/code>) like this:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">extension=php_pdo_pgsql.dll<\/code><\/span><\/pre>\n\n\n<p>After that, you need to restart the web server to apply the change.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='postgresql-data-source-name'>PostgreSQL data source name <a href=\"#postgresql-data-source-name\" class=\"anchor\" id=\"postgresql-data-source-name\" title=\"Anchor for PostgreSQL data source name\">#<\/a><\/h2>\n\n\n\n<p>The data source name or DSN holds database parameters that enable access to a database system. The data source name of the PostgreSQL consists of the following parameters:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>pgsql: is the DNS prefix.<\/li>\n\n\n\n<li>host: the host of the server where the PostgreSQL runs.<\/li>\n\n\n\n<li>port: the port to which PostgreSQL listens. The default PostgreSQL&#8217;s port is <code>5432<\/code>.<\/li>\n\n\n\n<li>dbname: the database name that you want to connect to.<\/li>\n\n\n\n<li>And other parameters.<\/li>\n<\/ul>\n\n\n\n<p>The following shows a DSN that connects to <code>dvdrental<\/code> database in the local PostgreSQL Server, port 5432:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">pgsql:host=localhost;port=5432;dbname=inventory;<\/code><\/span><\/pre>\n\n\n<h3 class=\"wp-block-heading\" id='connecting-to-a-postgresql-server'>Connecting to a PostgreSQL server <a href=\"#connecting-to-a-postgresql-server\" class=\"anchor\" id=\"connecting-to-a-postgresql-server\" title=\"Anchor for Connecting to a PostgreSQL server\">#<\/a><\/h3>\n\n\n\n<p>The following illustrates how to connect to the <code>dvdrental<\/code> database in PostgreSQL database server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n<span class=\"hljs-keyword\">require_once<\/span> <span class=\"hljs-string\">'config.php'<\/span>;\n\n<span class=\"hljs-keyword\">try<\/span> {\n\t$dsn = <span class=\"hljs-string\">\"pgsql:host=$host;port=5432;dbname=$db;\"<\/span>;\n\t<span class=\"hljs-comment\">\/\/ make a database connection<\/span>\n\t$pdo = <span class=\"hljs-keyword\">new<\/span> PDO($dsn, $user, $password, &#91;PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION]);\n\n\t<span class=\"hljs-keyword\">if<\/span> ($pdo) {\n\t\t<span class=\"hljs-keyword\">echo<\/span> <span class=\"hljs-string\">\"Connected to the $db database successfully!\"<\/span>;\n\t}\n} <span class=\"hljs-keyword\">catch<\/span> (PDOException $e) {\n\t<span class=\"hljs-keyword\">die<\/span>($e-&gt;getMessage());\n} <span class=\"hljs-keyword\">finally<\/span> {\n\t<span class=\"hljs-keyword\">if<\/span> ($pdo) {\n\t\t$pdo = <span class=\"hljs-keyword\">null<\/span>;\n\t}\n}\n<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>How the script works.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, make a new connection to the PostgreSQL database by creating a new instance of the PDO class.<\/li>\n\n\n\n<li>Second, show a message if the database connection is established successfully; otherwise, show an error message.<\/li>\n<\/ul>\n\n\n\n<p>The following option instruct PDO to set an error code and throw an exception if an error occurs:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">&#91;PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION]<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>PDO has three exception handling options:<\/p>\n\n\n\n<ul id=\"block-fa6bfa67-d1bb-41b7-834d-cf333924abcc\" class=\"wp-block-list\">\n<li><code>PDO::ERROR_SILENT<\/code> &#8211; PDO sets an error code for inspecting using the <code>PDO::errorCode()<\/code> and <code>PDO::errorInfo()<\/code> methods. The <code>PDO::ERROR_SILENT<\/code> is the default mode.<\/li>\n\n\n\n<li><code>PDO::ERRMODE_WARNING<\/code> &#8211; Besides setting the error code, PDO will issue an <code>E_WARNING <\/code>message.<\/li>\n\n\n\n<li><code>PDO::ERRMODE_EXCEPTION<\/code> &#8211; Besides setting the error code, PDO will raise a <code>PDOException<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>Note that PHP automatically closes the database connection when the script ends. If you want to close the database connection explicitly, you can set the PDO instance to null:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n$pdo = <span class=\"hljs-keyword\">null<\/span>;<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following <code>connect.php<\/code> script defines a <code>connect()<\/code> function that makes a connection to the PostgreSQL database and returns an instance of the PDO class:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n<span class=\"hljs-keyword\">require_once<\/span> <span class=\"hljs-string\">'config.php'<\/span>;\n\n<span class=\"hljs-function\"><span class=\"hljs-keyword\">function<\/span> <span class=\"hljs-title\">connect<\/span><span class=\"hljs-params\">(string $host, string $db, string $user, string $password)<\/span>: <span class=\"hljs-title\">PDO<\/span>\n<\/span>{\n\t<span class=\"hljs-keyword\">try<\/span> {\n\t\t$dsn = <span class=\"hljs-string\">\"pgsql:host=$host;port=5432;dbname=$db;\"<\/span>;\n\n\t\t<span class=\"hljs-comment\">\/\/ make a database connection<\/span>\n\t\t<span class=\"hljs-keyword\">return<\/span> <span class=\"hljs-keyword\">new<\/span> PDO(\n\t\t\t$dsn,\n\t\t\t$user,\n\t\t\t$password,\n\t\t\t&#91;PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION]\n\t\t);\n\t} <span class=\"hljs-keyword\">catch<\/span> (PDOException $e) {\n\t\t<span class=\"hljs-keyword\">die<\/span>($e-&gt;getMessage());\n\t}\n}\n\n<span class=\"hljs-keyword\">return<\/span> connect($host, $db, $user, $password);<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To use this <code>connect.php<\/code> in a script, you use the <code><a href=\"https:\/\/phptutorial.net\/php-tutorial\/php-require\/\">require<\/a><\/code> construct as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n\n$pdo = <span class=\"hljs-keyword\">require<\/span> <span class=\"hljs-string\">'connect.php'<\/span>;\n\n<span class=\"hljs-comment\">\/\/ start working with the database<\/span><\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enable PostgreSQL extension in <code>php.ini<\/code> file by removing the semicolon (<code>;<\/code>) from the line <code>extension=php_pdo_pgsql.dll<\/code><\/li>\n\n\n\n<li>Create a new instance of PDO by passing the data source name (DSN) to its constructor to make a connection to the PostgreSQL database server.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Did you find this tutorial useful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"136\"\n\t\t\t\tdata-post-url=\"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-postgresql\/\"\n\t\t\t\tdata-post-title=\"Connecting to PostgreSQL\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"136\"\n\t\t\t\tdata-post-url=\"https:\/\/www.phptutorial.net\/php-pdo\/pdo-connecting-to-postgresql\/\"\n\t\t\t\tdata-post-title=\"Connecting to PostgreSQL\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial shows you step by step how to connect to a PostgreSQL database server from PHP by using PDO.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":21,"menu_order":1,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-136","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/comments?post=136"}],"version-history":[{"count":4,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/136\/revisions"}],"predecessor-version":[{"id":3338,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/136\/revisions\/3338"}],"up":[{"embeddable":true,"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/pages\/21"}],"wp:attachment":[{"href":"https:\/\/www.phptutorial.net\/wp-json\/wp\/v2\/media?parent=136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}