Basic Statements

In this chapter, we will discuss basic SQL commands for PostgreSQL; these are Data Definition Language (DDL) commands and Data Manipulation Language (DML) commands. In basic terms, DDL commands are used to manage databases and tables, and DML commands are used to insert, delete, update, and select data inside databases. In this chapter, we will also discuss the psql environment, which refers to the interactive terminal for working with PostgreSQL. psql can be described as PostgreSQL's shell environment; it is the gate we have to go through in order to start writing commands natively in PostgreSQL. We have to remember that psql is always present in any PostgreSQL installation we work with. psql is a powerful environment in which to manage our data and our databases.

Basic statements and psql are therefore the foundations on which we will build our knowledge...

Technical requirements

You can find the code for this chapter in the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.

Setting up our developing environment

At this point in the book, we have learned how to install PostgreSQL and how to configure users. Let's now see how to connect to our database. In the next four steps, we will see how easy it is to do this:

1. Start by connecting to your psql environment:

postgres@pgdev:~$ psql 
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.
postgres=#

2. Next, switch on the expanded mode using the \x command:

postgres=# \x
Expanded display is on.

3. Then list all the databases that are present in the cluster:

postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | forumdb
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges | 

4. Finally, connect to the forumdb database:

postgres=# \c forumdb 
You are now connected to database "forumdb" as user "postgres".
forumdb=# 

Now, that we have finished setting up our developing...

Creating and managing databases

In this section, we will start by creating our first database, then we will learn how to delete a database and, finally, how to create a new database from an existing one. We will also analyze the point of view of the DBA. We will see what happens behind the scenes when we create a new database and learn some basic functions useful to the DBA to get an idea of ​​the real size of the databases.

Let's see how to create a database from scratch and what happens behind the scenes when a database is created.

Creating a database

To create the forumdb database from scratch, you will need to execute this simple statement:

CREATE DATABASE databasename

SQL is a case insensitive language, so we can write all the commands with uppercase or lowercase letters.

Now, let's see what happens behind the scenes when we create a new database. PostgreSQL performs the following steps:

1. Makes a physical copy of the template database, template1.
2. Assigns...

Managing tables

In this section, we will learn how to manage tables in the database.

PostgreSQL has three types of tables:

• Temporary tables: Very fast tables, visible only to the user who created them
• Unlogged tables: Very fast tables to be used as support tables common to all users
• Logged tables: Regular tables

We will now use the following steps to create a user table from scratch:

1. Create a new database using the following command:

forumdb=# create database forumdb2;
CREATE DATABASE

2. Execute the following command:

forumdb=# \c forumdb2
You are now connected to database "forumdb2" as user "postgres".

forumdb2=# CREATE TABLE users (
 pk int GENERATED ALWAYS AS IDENTITY
 , username text NOT NULL
 , gecos text
 , email text NOT NULL
 , PRIMARY KEY( pk )
 , UNIQUE ( username )
 );
CREATE TABLE

The CREATE TABLE command creates a new table. The command GENERATED AS IDENTITY, automatically assigns a unique value to a column.

3. Observe what was created on the database using the...

Understanding basic table manipulation statements

Now that you have learned how to create tables, you need to understand how to insert, view, modify, and delete data in the tables. This will help you update any incorrect entries, or update existing entries, as needed. There are a variety of commands that can be used for this, which we will look at now.

Inserting and selecting data

In this section, we will learn how to insert data into tables. To insert data into tables, you need to use the INSERT command. The INSERT command inserts new rows into a table. It is possible to insert one or more rows specified by value expressions, or zero or more rows resulting from a query. We will now go through some use cases as follows:

1. To insert a new user in the users table, execute the following command:

forumdb=# insert into users (username,gecos,email) values ('myusername','mygecos','myemail');
INSERT 0 1

This result shows that PostgreSQL has inserted one record into...

Summary

This chapter introduced you to the basic SQL/PostgreSQL statements and some basic SQL commands. You learned how to create and delete databases, how to create and delete tables, what types of tables exist, which basic statements to use to insert, modify, and delete data, and the first basic queries to query the database.

In the next chapter, you will learn how to write more complex queries that relate to multiple tables in different ways.

References

• The CREATE DATABASE official documentation: https://www.PostgreSQL.org/docs/12/sql-createdatabase.html
• The CREATE TABLE official documentation: https://www.PostgreSQL.org/docs/12/sql-createtable.html
• The SELECT official documentation: https://www.PostgreSQL.org/docs/12/sql-select.html
• The INSERT official documentation: https://www.PostgreSQL.org/docs/12/sql-insert.html
• The DELETE official documentation: https://www.PostgreSQL.org/docs/12/sql-delete.html
• The UPDATE official documentation: https://www.PostgreSQL.org/docs/12/sql-update.html
• The TRUNCATE official documentation: https://www.PostgreSQL.org/docs/12/sql-truncate.html