PostgreSQL Install and One Table Database – SQL CRUD tutorial for Ubuntu

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)
Posted in Uncategorized | Tagged , , , , , , , | Comments Off on PostgreSQL Install and One Table Database – SQL CRUD tutorial for Ubuntu

Comments are closed.