PostgreSQL is a popular database used often with Python Django, Python Flask and many other frameworks. It’s known for reliability and being very ACID.
In this tutorial, you’ll learn to
- Install PostgreSQL Server and Client on Ubuntu
- Create, read, update and delete data (CRUD)
Test Environment
If you already have a computer to run this on, jump straight to Three line install.
You don’t need to set up test environment. But this is what I used.
$ vagrant init ubuntu/trusty32 $ vagrant up $ vagrant ssh
If you have problems starting and installing VirtualBox and Vagrant on Ubuntu 14.04 host, see my fix.
The vagrant box incorrectly accepts client (host system) locale when using ‘vagrant ssh’. This results in errors, such as ‘createuser’ complaining “perl: warning: Setting locale failed.”. A simple fix (that should really be included in the trusty32 box) is to ignore client locale sent by SSH. This is only needed on vagrant, not on real servers. On the vagrant box:
$ sudoedit /etc/ssh/sshd_config
Find (ctrl-W) the line that allows client to pass locale, and comment it out:
#AcceptEnv LANG LC_*
Reload SSH, then log out and back.
$ sudo service ssh reload $ exit $ vagrant ssh
Three line install
$ sudo apt-get update
$ sudo apt-get -y install postgresql $ sudo -u postgres createdb $(whoami) $ sudo -u postgres createuser $(whoami)
The trick is to use your Linux system username for PostgreSQL database and PostgreSQL user. Then authentication is automatic.
$ psql
Getting Help
Help is available with ‘help’:
tero=> help [..] \h for help with SQL commands \? for help with psql commands [..] \q to quit
PostgreSQL internal commands use the format backslash ‘\’ and some letters. PostgreSQL command help is backslash – question mark ‘\?’. It uses ‘less’ to show help, so you can exit help with “q”. PostgreSQL also has a full SQL reference built in:
tero=> \h SELECT
With this help, we can move to CRUD: create, read, update, delete.
C: CREATE TABLE
tero=> CREATE TABLE students (id SERIAL PRIMARY KEY, name VARCHAR(200)); CREATE TABLE
For every table, you should set a SERIAL id field as the PRIMARY KEY. For example, when you create links that refer to database items, it’s much easier to work with integers than UTF-8 strings containing weird characters.
Use type SERIAL for id, so that numbers are automatically handled by the database management system. In this way, Postgre SERIAL is similar to MySQL AUTO_INCREMENT. SERIAL type is long enough for your database. Four bytes means billions (x*1e9) of unique numbers.
Let’s view our new tables (aka relations):
tero=> \d List of relations Schema | Name | Type | Owner --------+-----------------+----------+------- public | students | table | tero public | students_id_seq | sequence | tero (2 rows)
And describe table structure
tero=> \d students
Table "public.students"
Column | Type | Modifiers
--------+------------------------+-------------------------------------------------------
id | integer | not null default nextval('students_id_seq'::regclass)
name | character varying(200) |
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
C: Create Records: INSERT
tero=> INSERT INTO students(name) VALUES ('Tero');
INSERT 0 1
Parenthesis around values are required. You must use single quotes ”’, double quotes are not accepted.
Let’s insert a couple more
tero=> INSERT INTO students(name) VALUES ('Matti');
tero=> INSERT INTO students(name) VALUES ('Maija');
When creating web apps, you often need the id of the record you created. For example, you might want to create a record and then open it for editing.
tero=> INSERT INTO students(name) VALUES ('Liisa') RETURNING id, name;
id | name
----+-------
5 | Liisa
(1 row)
R: Read: SELECT
tero=> SELECT * FROM students; id | name ----+------- 1 | Tero 2 | Matti 4 | Maija 5 | Liisa (4 rows)
SQL makes it easy to just show some records. Typically, the criteria is numbers: “… WHERE year>2003”. For demonstration purposes we use “LIKE” here, but it’s not very efficient in real life as SQL servers are usually slow for full text search.
tero=> SELECT * FROM students WHERE name LIKE 'Ma%'; id | name ----+------- 2 | Matti 4 | Maija (2 rows)
U: UPDATE
tero=> UPDATE students SET name='Tero Karvinen' WHERE name='Tero'; UPDATE 1
You must use single quotes ‘ instead of double quotes.
tero=> SELECT * FROM students; id | name ----+--------------- 2 | Matti 4 | Maija 5 | Liisa 1 | Tero Karvinen (4 rows)
D: DELETE
tero=> DELETE FROM students WHERE name='Liisa'; DELETE 1 tero=> SELECT * FROM students; id | name ----+--------------- 2 | Matti 4 | Maija 1 | Tero Karvinen (3 rows)
Well Done
You can now
- Install PostgreSQL on Ubuntu (with just 3 commands)
- Create, Read, Update and Delete records (CRUD)