- Database Installation
- Add Database User
- Connect To Database
- CREATE TABLE
- INSERT Records
- SELECT Records
- UPDATE Records
- DELETE Records
- JOIN Tables
- Backup Table
- Backup Database
- Restore From Backup
- List All Databases
- Connect To Database
- Show Database Tables
- Find Column in Database
- SELECT pgsql version
- DELTE FOREIGN KEY
- List FOREIGN KEYSs for a table
- ADD ON DELETE CASCADE
- RESET SEQUENCE
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.
Because this installation is on CentOS the database and related libs are set up with yum.
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 ]
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.
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.
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)
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');
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.
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".
Deleting a recored is even easier that creating one, so be careful.
my_db=> DELETE FROM articles WHERE article_id=2; DELETE 1
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.
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:
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:
Even easier than dumping a database, is restoring it, simply execute this command.
psql -U postgres my_db < my_db.sql
There are two methods to do this..
To list all the tables in a database..
Search the database for a specied field name..
To see which version of postgresql you are running.
To DELETE a 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.
Show a list of all FOREIGN KEYS for a given table name
Add ON DELETE CASCADE to an existing table.
ALTER TABLE table_one add constraint "name_of_fkey" FOREIGN KEY(name_of_column) REFERENCES table_two ON DELETE CASCADE;
Reset a sequence to one.