[adsense id=”0514458240″ width=”468″ height=”60″]
This is a post to show how to setup a database in PostgreSQL and PHP source code to access the table and return the results from the query.
For those of you coming from MySQL or Oracle background the steps involved are similar in some cases but different in others. On Linux the application to access PostgreSQL databases is called psql. This takes a single parameter, the database with which you wish to connect with. This parameter is optional.
psql [dbname]
Once inside the psql application to connect to a or another database you must issue the command:
user=# \c {dbname}
MySQL database tables use the AUTO_INCREMENT option to allow fields to be populated by a unique number. PostgreSQL does not offer the same syntax but as with Oracle does offer SEQUENCES.
user=# CREATE SEQUENCE {sequence name};
CREATE SEQUENCE
To view all the sequences in the database that you are connected to you can issue:
user=# \ds
or list of tables
user=# \dt
To associate the sequence to a particular table column then you issue the following command:
user=# ALTER TABLE {table name} ALTER COLUMN {column name} SET DEFAULT NEXTVAL('{sequence name}');
ALTER TABLE
Any inserts taking place on this table automatically add the next value of the sequence in cases where the column is left null.
The code needed for access the postgreSQL database from PHP uses pg_connect, pg_query and pg_fetch_row whose functions are similar to those of mysql_connect, mysql_query and mysql_fetch_row.