PostgreSQL Part II

Asif Ahmed

Last time, I ended up covering the Quickstart part of the Postgres with PHP tutorial on Lynda.com. This was the reason it was so accelerated. I was wondering why this course was just throwing me into files with tons of PHP code without explaining each step from the beginning. The following sections in the tutorial actually try to teach us more thoroughly. Again this post will mostly serve as a set of notes for myself.

Creating a table:

CREATE TABLE foo (animal TEXT, sound TEXT);

This creates a table called “foo” with columns “animal” and “sound”

Insert data:

INSERT INTO foo (animal,sound) VALUES ('dog','woof');

This inserts a data into a table called “foo” with animal = dog and sound – woof.

Look at data:

SELECT * FROM foo;

This shows us all the data in table “foo”.

Select data:

SELECT * FROM album;
SELECT title, artist, label from album;
SELECT artist AS 'Artist', title AS 'Album', released AS 'Release Date' FROM album

The first line selects and shows everything from the table. The second line selects the title, artist and label columns from the table. Third line displays the artist, title, and released columns from the table, but it displays them under the headings “Artist”, “Album”, and “Release Date”.

The next few sections also cover numerical and character data types in PostgreSQL. There are a bunch of numeric datatypes but it’s important to note that we shouldn’t use floating point money and only use the “numeric” data type.

Find data based on some pattern matching:

SELECT * FROM City WHERE Name ILIKE 'z%' ORDER BY Name;
SELECT * FROM City WHERE Name ILIKE '_w%' ORDER BY Name;
SELECT * FROM City WHERE Name SIMILAR TO '[ZK]w_+' ORDER BY Name;

The first line will select from the city table where all names start with the letter Z. “ILIKE” means that it the matching is not case sensitive. Also the “%” sign means that it can match any number of characters. In the second expression we use an underscore. This is a wildcard. This means that we are matching for anything that has the second letter as a “w”. The third expression attempts to be more regex-like. It tries to get all names where the first letter is either a ‘Z’ or a ‘K’ and the second letter is a ‘w’.