10. Introduction to PostgreSQL

This will show how to install PostgreSQL and access it using the SQL Shell. We can look at connecting it with Django, etc, later.

Useful video: https://www.youtube.com/watch?v=BLH3s5eTL4Y

Go to https://www.enterprisedb.com/downloads/postgres-postgresql-downloads and download the appropriate file.

Make a note of the superuser password and port (default is 5432).

Once it hast installed, go to Start and scroll down to the PostgreSQL folder. There is a terminal problem here called SQL Shell (psql).

It asks for a server, database, port, username. It’s OK to use the defaults for now. Then enter password. If it all goes well you should see something like this:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (14.0)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

To list the databases in the server type \l

We should see something like this:

                                                  List of databases
   Name    |  Owner   | Encoding |           Collate           |            Ctype            |   Access privileges
-----------+----------+----------+-----------------------------+-----------------------------+-----------------------
 postgres  | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
 template0 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
(3 rows)

We can see that there are three databases here – we just created postgres. If we want to create a brand new database we can type:

CREATE DATABASE test;

\l will then show 4 databases. If we were to close SQL Shell, and reopen, we can go to the test database by typing in test (second prompt) and the password (fifth prompt) and then we will see this at the end of it:

test=#

This shows we are inside the test database now.

We can create a table by:

CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(50) NOT NULL
);

This should return CREATE TABLE and we can type

\d

to look at all of the relationships:

              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | person        | table    | postgres
 public | person_id_seq | sequence | postgres
(2 rows)

We can see the table person has been created – it’s in the first line. The second line reference the id BIGSERIAL (not by name) which is auto incrementing. This is listed because it’s also a relationship. If we just want to look at the tables we can use \dt

         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | person | table | postgres
(1 row)

We can data by doing this:

INSERT INTO person (name, country) VALUES ('John Smith', 'UK');

This returns INSERT 0 1

We can view our data by doing this:

SELECT * FROM person;

And we will get this:

 id |    name    | country
----+------------+---------
  1 | John Smith | UK
(1 row)

This shows that the database is up and running and later we can see more complex commands.

Wednesday 27 October 2021, 619 views

Next post: 11. Building a database using SQL
Previous post: 9. How to start writing a new application in Django