PostgreSQL Basics

NegarNegar
9 min read

Table of contents

Install PostgreSQL on your system. you can find the installers for various platforms in this link.

Difference between PSQL and SQL commands

First of all, lets differentiate between PostgreSQL and SQL commands:

  • Any command starting with backslash \ is a PostgreSQL command.

    For example, commands like getting a list of databases \l, getting a list of tables \dt, getting a table schema \d+ <TableName> and so forth are all PostgreSQL commands since they are starting with \

  • otherwise, its SQL command.

    For Instance, commands like SELECT FROM <TableName>, DROP TABLE <TableName>, DELETE * FROM <TableName> WHERE <ConditionDescription> and so forth are all SQL command.

  • Since here we are using PSQL client, any command that starts with psql mean that we are asking the client to do some task. Therefore, If you are using another client, you should substitute psql part of that command with the name of your PSQL client.

In what follows, I won't explicitly state whether a command is a PSQL or SQL one.

(Dis)Connecting to databases

Connect to the default database

After Installation is completed, connect to the default server using the following commands:

C:\>psql -U postgres
psql (16.1)
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=#

The postgres=# prompt show that we have connected to the "postgres" database, and the # shows that we have connected to this database with a superuser user.

Connect to another specific database

To connect to any database we should use psql command.

-d DatabaseName : The first parameter of this command is "database name"

-U DatabaseUser : The second parameter is name of the user with whom credentials' we want to connect to the database.

-W : push the server to ask for password

C:\>psql -d people -U pg4e -W
Password:
psql (16.1)
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.

people=>

The people=> prompt show that we have connected to the "people" database, and the > shows that we have connected to this database with a non-superuser user.

Connecting to a database on another host

To connect to any database on another host we should use the following command:

-h <HostAddress> : Specifies Address of the host on which the database is.

-p <PortNumber> : Specifies the port number to connect to that host

-d <DatabaseName> : Specifies "database name"

-U <DatabaseUser> : Specifies name of the user with whom credentials' we want to connect to the database.

-W : push the server to ask for password

C:\>psql -h example.com -p 5432 -d people -U pg4e -W
Password:
psql (16.1)
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.

people=>

The people=> prompt show that we have connected to the "people" database, and the > shows that we have connected to this database with a non-superuser user.

Disconnect (quit) a session

In order to quit an PostgreSQL session use the following command:

postgres=# \q

Creating and listing database, table and user

Get the database version

Lest get the version of you PostgreSQL database:

SELECT version();

Get list of databases

Now get list of default databases created by the installer while installing the PostgreSQL database:

postgres=# \l
                                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |          Collate           |           Ctype            | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           |
 template0 | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | =c/postgres          +
           |          |          |                 |                            |                            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | =c/postgres          +
           |          |          |                 |                            |                            |            |           | postgres=CTc/postgres
(3 rows)

Create a new Database

Use the following command to create a database named "people" whose owner is "pg4e" user:

postgres=# CREATE DATABASE people WITH OWNER 'pg4e';
CREATE DATABASE

Create a new non-superuser User

Use the Following command to create a user named "pg4e" with password "secret"

postgres=# CREATE USER pg4e WITH PASSWORD 'secret';
CREATE ROLE

Create tables in the database

Top create a table in your database use the CREATE TABLE command. Here, we define the schema of a table. We create users table which has two attributes/fields/columns, name and email. Each of these attributes are defined as character strings with max of 128 characters

people=> CREATE TABLE users(
people(> name VARCHAR(128),
people(> email VARCHAR(128)
people(> );
CREATE TABLE
people=>

List tables in the database

To list tables/relations in the database use to he following command:

people=> \dt
Did not find any relations.

if you haven't created any tables in your database yet, you will get "Did not find any relations." message. Otherwise, you will get list of existing tables like what follows:

people=> \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | users | table | pg4e
(1 row)

people=>

Get the schema of a table

Lets suppose we have forgotten what was the schema (the definition of the table and its attributes/columns), and we want to review the schema one more time. In this case we can use the following command:

people=> \d+ users
                                                  Table "public.users"
 Column |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name   | character varying(128) |           |          |         | extended |             |              |
 email  | character varying(128) |           |          |         | extended |             |              |
Access method: heap

people=>

Basic CRUD commands

INSERT/create row(s) into the table

Suppose we wan to insert several rows/objects into the table. We have to use the following command:

people=> INSERT INTO users (name, email) VALUES ('Peter', 'peter@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('Terry', 'terry@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('Eliza', 'eliza@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('John', 'john@example.edu');
INSERT 0 1

READ/SELECT row(s) from a table

In order to read the rows in a table we should use SELECT command:

people=> SELECT * FROM users;
 name  |       email
-------+-------------------
 Peter | peter@example.edu
 Terry | terry@example.edu
 Eliza | eliza@example.edu
 John  | john@example.edu
(4 rows)

We can include some criteria in the SELECT command as well:

people=> SELECT * FROM users WHERE email='terry@example.edu';
 name  |       email
-------+-------------------
 Terry | terry@example.edu
(1 row)

UPDATE row(s) in a table

In order to update one/more piece(s) of information in one/more row(s), we use UPDATE command like what follows:

people-> UPDATE users SET name='Johnny' WHERE email='john@example.edu';
UPDATE 1

Hint: this command will update any number of rows that meet its criteria.

Lets see how this command changed the last entry of users table:

people=> SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Terry  | terry@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
(4 rows)

DELETE row(s) from a table

To remove one/more row(s) from a table, use the DELETE command like what follows:

people=> DELETE FROM users WHERE email='terry@example.edu';
DELETE 1

Hint: this command will delete any number of rows that meet its criteria.

Lets see how this command removed an entry of users table:

people=> SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
(3 rows)

Sorting

In order to sort list of objects we can use ORDER BY clause in SELECT statements. By default it sort the object by ascending order. However, you can explicitly state the order using ASC or DESC to sort the objects in ascending or descending manner respectively.

In the following example, you can see that at first the objects are not sorted.

people=> SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
(3 rows)

Then, when we use ORDER BY clause, it by default sorts the objects in ascending manner. Also, notice that adding the ASC at the end of the command leads to the same result.

people=> SELECT * FROM users ORDER BY email;
  name  |       email
--------+-------------------
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
 Peter  | peter@example.edu
(3 rows)

people=> SELECT * FROM users ORDER BY email ASC;
  name  |       email
--------+-------------------
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
 Peter  | peter@example.edu
(3 rows)

You can also change the order by stating DESC at the end of the command.


people=> SELECT * FROM users ORDER BY email DESC;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Johnny | john@example.edu
 Eliza  | eliza@example.edu
(3 rows)

Wildcards/Like clause

In order to find specific objects we can use LIKE operator in WHERE clauses. For example, to find all the users in whose name have e, we can use the following command:

people=> SELECT * FROM users WHERE name LIKE '%e%'
;
 name  |       email
-------+-------------------
 Peter | peter@example.edu
 Terry | terry@example.edu
(2 rows)

Keep in mind that as a result of LIKE operator, the WHOLE table will be scanned and the database cannot use indexes to find the records. Therefore, it is a very slow operation.

Paging/skipping objects

You can ask the database to limit the number objects in the list by using LIMIT clause. Also you can ask the database to skip a specific number of objects from the beginning of the list by using OFFSET clause.

It is worth mentioning that WHERE and ORDER BY clauses will be executed before LIMIT and OFFSET clauses. In addition note that OFFSET and LIMIT are both very fast and efficient clauses.

Lets see the initial list of the objects in the users table in both orders.

people=> SELECT * FROM users ORDER BY email ASC;
  name  |       email
--------+-------------------
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
 Peter  | peter@example.edu
 Terry  | terry@example.edu
(4 rows)

people=> SELECT * FROM users ORDER BY email DESC;
  name  |       email
--------+-------------------
 Terry  | terry@example.edu
 Peter  | peter@example.edu
 Johnny | john@example.edu
 Eliza  | eliza@example.edu
(4 rows)

LIMIT clause

people=> SELECT * FROM users ORDER BY email DESC LIMIT 2;
 name  |       email
-------+-------------------
 Terry | terry@example.edu
 Peter | peter@example.edu
(2 rows)

OFFSET clause

OFFSET starts indexing from 0, therefore index 1 will be the second object in the list.

people=> SELECT * FROM users ORDER BY email OFFSET 1 LIMIT 2;
  name  |       email
--------+-------------------
 Johnny | john@example.edu
 Peter  | peter@example.edu
(2 rows)

Counting

In order to count the number of rows in a result of a query, you can use COUNT clause. COUNT is actually faster than WHERE.

people=> SELECT COUNT(*) FROM users;
 count
-------
     4
(1 row)

people=> SELECT COUNT(*) FROM users WHERE name LIKE '%e%';
 count
-------
     2
(1 row)

Well, this is the very core of PostgreSQL and I will continue this topic in the further articles.

P.S. The cover is generated by DALL-E.

0
Subscribe to my newsletter

Read articles from Negar directly inside your inbox. Subscribe to the newsletter, and don't miss out.

PostgreSQLpsql

Written by

Negar
Negar