PHPRO.ORG

Introduction To PostgreSQL

08 May

Introduction To PostgreSQL

By Kevin Waterson 1005796 12 Comments
  1. Abstract
  2. Database Installation
  3. Add Database User
  4. Connect To Database
  5. CREATE TABLE
  6. INSERT Records
  7. SELECT Records
  8. UPDATE Records
  9. DELETE Records
  10. JOIN Tables
  11. Backup Table
  12. Backup Database
  13. Restore From Backup
  14. List All Databases
  15. Connect To Database
  16. Show Database Tables
  17. Find Column in Database
  18. SELECT pgsql version
  19. DELTE FOREIGN KEY
  20. List FOREIGN KEYSs for a table
  21. ADD ON DELETE CASCADE
  22. RESET SEQUENCE

Abstract

This tutorial introduces the user to the postresql database and how to interface with it. The system used for this demonstration is CentOS but mostly the information is generic to any distribution or operating system. Before launching into using PHP to interface with PostgreSQL, this tutorial aims to introduce the user to some simple operations on getting a postgreSQL database installed, creating a database, and performing basic CRUD (Create Retreive Update Delete) operations.

It is hoped that a PHP user with a grounding in the basics will better understand what PHP is doing when using pg_* suite of functions, or, for the more evolved, using PDO as an interface.

Database Installation

Because this installation is on CentOS the database and related libs are set up with yum.

# yum install postgresql postgresql-devel

PHP will need to compiled to support postgresql if it is not already. This requires --with-pgsql to be added to the config. Or, if on windows, uncommenting the related line in php.ini.

For users with access to yum, installing the required php-pgsql package is as simple as: 

yum install php-pgsql

Starting the postgresql server on CentOS is simply a matter of service postgresql start or start directly from init. 

# /etc/rc.d/init.d/postgresql start
Initializing database:                                     [  OK  ]
Starting postgresql service:                               [  OK  ]

Add Database User

Now postgresql is ready to accept commands. To gain access to the database engine, the psql command is provided. If this command is run at this stage, without any databases having been created, an such as this will ensue. 

psql: FATAL:  database "root" does not exist

To avoid this, su to the postres user and from there, a username and password, or in postresql parlance, a ROLE can be created. The default database template1 is available and helps us connect. 

su - postgres
-bash-3.1$ psql template1
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# create user bruce password 'snurgs';
template1=# create user bruce password 'snurgs';
CREATE ROLE

Note, to remove a user, simply use the drop user command. 

DROP USER bruce;

Now a database can be created. This is done with the createdb command as follows 

-bash-3.1$ createdb my_db;
CREATE DATABASE
-bash-3.1$

In the above snippet, a database named my_db has been created. To grant bruce access to the database, he must be added to the pg_hba.conf file. 

# allow all on localhost to use  md5 password auth
local   all         all                             md5
# add bruce
host   my_db     bruce         127.0.0.1/32          md5
host   my_db     bruce         192.168.0.111         md5

The first of these three lines is a catch-all line that allows any user on localhost to use md5 aulthentication to connect to a database. The second and third lines allow bruse to use my_db on the two IP addresses stated.

With this in place, the pg_hba.conf file needs to be reloaded to read the new values. This can simply done by reloading from init. 

/etc/rc.d/init.d/postgresql reload

Connect To Database

Finally, a connection to the my_db database can be made as the user bruce. 

#  psql -h 127.0.0.1 -U bruce my_db
Password for user bruce: 
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

my_db=>

Nothing will happen at this stage of the game, as the database is empty.

CREATE TABLE

Like SQL Database, Postgresql follows the SQL 92 standard (mostly) and creating a table is the same as for other databases. The most important for many users is creating an auto incremented serial number on tables. This is acheived in pgsql with the SERIAL PRIMARY KEY statement. This will create a second sequence table named users_user_id_seq which will hold the auto incremented sequence of numbers. Also, a users_user_name_key index will be created for the user_name field, which is defined as UNIQUE. 

CREATE TABLE users (
	user_id SERIAL PRIMARY KEY NOT NULL,
	user_name varchar(40) default NULL,
	user_password varchar(40) default NULL,
	UNIQUE (user_name)
);

CREATE TABLE articles (
	article_id SERIAL PRIMARY KEY NOT NULL,
	article_title varchar(255) default NULL,
	article_text text,
	article_date TIMESTAMP DEFAULT now(),
	article_publish boolean DEFAULT false,
	user_id int REFERENCES users(user_id) ON DELETE CASCADE
);

When the articles table is created, a sequence is also created as with the users table. This sequence will be called articles_article_id_seq. Note that all sequences follow the naming convention <table name>_<field name>_seq.

To see the contents of the database now, simply use the \d command and you will see this 

my_db=> \d
                       List of relations
 Schema |               Name                |   Type   | Owner 
--------+-----------------------------------+----------+-------
 public | articles                          | table    | bruce
 public | articles_article_id_seq           | sequence | bruce
 public | users                             | table    | bruce
 public | users_user_id_seq                 | sequence | bruce
(4 rows)

INSERT Records

To INSERT a record is just the same as with all databases, and uses INSERT. First, lets add a few users. 

INSERT INTO USERS ( user_name, user_password) VALUES('peter', '0beec7b5ea3f0fdbc95d0dd47f3c5bc275da8a33');
INSERT INTO USERS ( user_name, user_password) VALUES('paul', '62cdb7020ff920e5aa642c3d4066950dd1f01f4d');
INSERT INTO USERS ( user_name, user_password) VALUES('mary', 'bbe960a25ea311d21d40669e93df2003ba9b90a2');
When these queries are run in the terminal, a response tells if the query was executed.

SELECT Records

Now that there is data in the database, it can be retreived using the SELECT statement. This is simply done like this: 

SELECT user_id, user_name FROM users;

There is a shortcut to selecting ALL values from the table, however, its use is discouraged as there is not need to be extracting data into memory that is not going to be used. This is provided for example only. 

SELECT * FROM users;

When the first query is run the response will be all the results that match the query, that is, all the user names and user_id's. 

my_db=> SELECT user_id, user_name FROM users;
 user_id | user_name 
---------+-----------
       1 | peter
       2 | paul
       3 | mary
(3 rows)

Now lets insert a few articles... 

INSERT INTO articles (article_title, article_text, user_id) VALUES ('My First Article', 'This is a story about my first article', 3);
INSERT INTO articles (article_title, article_text, user_id) VALUES ('My Second Article', 'This is the second article', 1);

When the contents of the article table is viewed with SELECT, the values of the article_id, article_date, and article_publish field has been automatically filled with the default values.

UPDATE Records

The article_publish field has a default value of false, and when viewed, has an "f" in the field. If this article needs to be published, this field will need to be updated to true. To UPDATE a field value, the syntax is like this.. 

UPDATE articles SET article_publish=true WHERE article_id=2;

Now when viewed with SELECT, the article_publish field has a value of "t".

DELETE Records

Deleting a recored is even easier that creating one, so be careful. 

my_db=> DELETE FROM articles WHERE article_id=2;
DELETE 1

JOIN Tables

Now that basic CRUD (Create Retreive Update Delete) functionality is done, the ability to SELECT records from multiple tables can be addressed. If the contents of articles were selected, but the user_name field from the users table was required to check who the author is, the anwser is to JOIN the articles and users table.

When the articles table was created, a user_id field was created with a foreign key to the user_id field in the users table. This can now be utilised to JOIN them together as if they were a single table. 




Backup Table



To back up a single table in pgsql is quite simple, here the articles table is backed up from the my_db database and dumped into a file named articles.sql. Run this command from the command line.





# pg_dump -U bruce -a -t articles my_db > articles.sql 
Password: 






Backup Database



To back up the whole database is just as easy, just omit the table name. This time the file will be named the same as the database, which is a good convention to follow.





# pg_dump -U bruce -a  my_db > data-dump.sql 
Password: 







Restore Database From Backup



Even easier than dumping a database, is restoring it, simply execute this command.



psql -U postgres my_db < my_db.sql 







List All Datbases


There are two methods to do this..




\l 


psql -l




Connect To Database




\c database_name





List Database Tables


To list all the tables in a database..




/dt




Find Column in Database


Search the database for a specied field name..



SELECT table_name from information_schema.columns where column_name = 'quota'; 





SELECT version


 To see which version of postgresql you are running.



SELECT version();





DELETE FOREIGN KEY


 To DELETE a foreign key.



ALTER TABLE table_name DROP CONSTRAINT name_of_foreign_key;





Add FOREIGN KEY


 To ADD a foreign key to an existing table in pgsql.


In this example table_two_id is a column in table_one, which references table_two.



ALTER TABLE table_one ADD FOREIGN KEY (table_two_id) REFERENCES table_two;




Show List of FOREIGN KEYs


 Show a list of all FOREIGN KEYS for a given table name



SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='name_of_your_table';





Add ON DELETE CASCADE


 Add ON DELETE CASCADE to an existing table.



ALTER TABLE table_name DROP CONSTRAINT name_of_fkey;

ALTER TABLE table_one add constraint "name_of_fkey" FOREIGN KEY(name_of_column) REFERENCES table_two ON DELETE CASCADE;





RESET SEQUENCE


 Reset a sequence to one.



ALTER SEQUENCE users_id_seq RESTART;