# PostgreSQL Basics Install PostgreSQL on your system. you can find the installers for various platforms in this [link](https://www.postgresql.org/download/). ## 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+ ` and so forth are all PostgreSQL commands since they are starting with `\` * otherwise, its SQL command. For Instance, commands like `SELECT FROM `*,* `DROP TABLE `*,* `DELETE * FROM WHERE ` 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: ```pgsql 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 ```pgsql 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 ` : Specifies Address of the host on which the database is. `-p ` : Specifies the port number to connect to that host `-d ` : Specifies "database name" `-U ` : Specifies name of the user with whom credentials' we want to connect to the database. `-W` : push the server to ask for password ```pgsql 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: ```pgsql postgres=# \q ``` ## Creating and listing database, table and user ### Get the database version Lest get the version of you PostgreSQL database: ```pgsql SELECT version(); ``` ### Get list of databases Now get list of default databases created by the installer while installing the PostgreSQL database: ```pgsql 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: ```pgsql 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" ```pgsql 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 ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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: ```pgsql 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. ```pgsql 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. ```pgsql 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. ```pgsql 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: ```pgsql 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. ```pgsql 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 ```pgsql 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. ```pgsql 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`. ```pgsql 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.