Basic PostgreSQL Query for Beginner

The first thing you need to learn when learning PostgreSQL is how to create databases, tables, browse data, update data, and even delete data using statements. In this article, we will learn about those basic SQL queries in PostgresSQL.

Installation

To install PostgreSQL on macOS, you can use Homebrew. To install the latest version of PostgreSQL, use the following command:

brew install postgresql

If you want to install a specific version of PostgreSQL, you can use the following command, replacing 14 with the desired version number:

brew install postgresql@14

To start the PostgreSQL service, run the following command:

brew services restart postgresql@14

Database connection

To start working with PostgreSQL database you can use interactive query shell psql or gui tools like Tableplus, Squelpro or others. But in this article we will go with psql.

I will assume you have already downloaded and installed postgres, but if not, go to this page and follow the instructions for installation.

After done with the installation you can start connect to PostgresSQL using this command.

psql -h localhost -U postgres -d postgres -p 5432

If your console showing information like this, it mean all is good we can start to run sql query there.

$ psql -h localhost -U postgres -d postgres -p 5432
Password for user postgres: 
psql (10.17)
WARNING: Console code page (437) 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=#

Basic Postgresql CLI Command

Show all databases

To list all databases in a PostgreSQL server, you can use the following command:

\l

This will show a list of all databases on the server, as well as some additional information such as the owner of the database and the encoding used.

Alternatively, you can use the following command to list all databases:

SELECT datname FROM pg_database;

This will return a list of the names of all databases in the server.

Keep in mind that you need to be connected to a PostgreSQL server to be able to list its databases. You can connect to a server using the psql command-line interface or a graphical client such as pgAdmin.

Show all tables

To list all tables in a specific database in PostgreSQL, you can use the following command:

\dt

This will show a list of all tables in the current database.

Alternatively, you can use the following command to list all tables in a specific database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

This will return a list of the names of all tables in the public schema of the current database.

Keep in mind that you need to be connected to a PostgreSQL database to be able to list its tables. You can connect to a database using the psql command-line interface or a graphical client such as pgAdmin.

Create database

First thing you need to create database name, in Postgres we can have multiple database name on single server so before working with the data we need to create a database name first.

CREATE DATABASE basic_postgres;

So here's the rules that you need to remember:

• You can't use character - when creating database name e.g.: create database basic-postgres;
• If you want to use char - you wrap it with double quote e.g.: create database "basic-postgres";
• You can't create database name using number e.g: create database 1234;
• If you need a number use alphanumeric e.g.: create database project1;

More about PostgreSQL data type, read here.

After creating the database we need to select database before we create table and play with the data, it's like creating folder then go inside that folder.

For that we can use this query.

\c basic_postgres;

If your console shows this, it means the database has already been created and now you can run sql query here.

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

Create table

Now let's create a simple users table to store user information like email, password, email etc. You will find that you will work with this table when you are working on website that have login system in it.

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL DEFAULT '',
    email VARCHAR(150) NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT users_email_key UNIQUE (email)
);

Let's explore what happen inside query above.

• id BIGSERIAL PRIMARY KEY column id will have data type of integer and with primary key it will make this field can be connected with others table usualy will be used to query join.
• username VARCHAR(100) NOT NULL DEFAULT '' column username will have string with max length 100 if we not specify it will be 255 by default, and we are set the default value to empty string.
• email VARCHAR(150) NOT NULL column email can not be empty because we don't set the default value here.
• created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() in this column we are will store a date data with the timezone for the user.

The data inside users table will look like this.

id  |  username  |     email      |   password   |         created_at
----+------------+----------------+--------------+------------------------------
 1  | ahmadrosid | ahmad@mail.com | ZZx9skKNMaYe | 2021-09-22 23:37:59.327921+07

And if you want to see list of table inside the database you can use this query.

basic_postgres=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | users        | table    | postgres | 8192 bytes |
(1 rows)

Insert data

To insert data into a table in PostgreSQL, you can use the INSERT statement. The basic syntax for the INSERT statement is:

INSERT INTO "public"."users" ("username", "email", "password") 
VALUES ('ahmadrosid', 'alahmadrosid@gmail.com', 'ZZx9skKNMaYe') RETURNING *;

Here, "users" is the name of the table that you want to insert data into, and username, email, password, etc. are the names of the columns in which you want to insert the data. The VALUES clause specifies the values to be inserted into the columns.

If you are inserting data into all of the columns in the table, you can specify the values in the order that the columns appear in the table. If you are only inserting data into a subset of the columns, you must specify the values in the same order as the column names listed in the INSERT statement.

Get data from table

The SELECT statement is used to retrieve data from a database in PostgreSQL. The * in the SELECT clause indicates that you want to retrieve all columns from the users table. The FROM clause specifies the name of the table from which you want to retrieve the data.

SELECT * FROM users;

In this example, the SELECT statement will retrieve all rows and all columns from the users table. The resulting data set will include all columns and all rows in the users table.

basic_postgres=# select * from users;
 id |  username  |     email      |   password   |         created_at
----+------------+----------------+--------------+------------------------------
 1  | ahmadrosid | ahmad@mail.com | ZZx9skKNMaYe | 2021-09-22 23:37:59.327921+07
(1 row)

If you only want to retrieve a subset of the columns from the table, you can specify the specific column names instead of using the * wildcard. For example, to retrieve only the id and name columns from the users table, you can use the following SELECT statement:

SELECT id, name FROM users;

You can also use the WHERE clause to filter the rows that are returned by the SELECT statement. For example, to retrieve only the rows from the users table where the id column is greater than 5, you can use the following SELECT statement:

SELECT * FROM users WHERE id > 5;

The SELECT statement is a powerful and flexible way to retrieve data from a database in PostgreSQL. You can use it to retrieve all columns and rows from a table, or you can use it to retrieve only a subset of the columns and rows that meet specific criteria.

Update data

The UPDATE statement is used to modify data in a database in PostgreSQL. The basic syntax for the UPDATE statement is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here, table_name is the name of the table that you want to update, and column1, column2, etc. are the names of the columns that you want to update. The SET clause specifies the new values for the columns, and the WHERE clause specifies the conditions that must be met for the update to be applied.

In this example, the UPDATE statement will update the username column of the users table to have a value of 'rosid' where the id column has a value of 1. This will modify the username value for the row with an id of 1 to be 'rosid'.

It's important to note that the WHERE clause is optional in the UPDATE statement. If you omit the WHERE clause, the update will be applied to all rows in the table. For example, the following UPDATE statement will set the username column of all rows in the users table to have a value of 'rosid':

UPDATE "public"."users" SET "username" = 'rosid' WHERE "id" = 1;

It's usually a good idea to include a WHERE clause in your UPDATE statements to avoid accidentally modifying data that you don't intend to modify.

Delete data

The DELETE statement is used to remove data from a database in PostgreSQL. The basic syntax for the DELETE statement is:

DELETE FROM table_name WHERE condition;

Here, table_name is the name of the table from which you want to delete data, and condition is a condition that specifies which rows to delete. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

In this example, the DELETE statement will delete all rows from the users table where the id column has a value of 1. This will remove any rows from the users table that have an id of 1.

It's important to note that the WHERE clause is optional in the DELETE statement. If you omit the WHERE clause, the DELETE statement will delete all rows in the table. For example, the following DELETE statement will delete all rows in the users table:

DELETE FROM users;

It's usually a good idea to include a WHERE clause in your DELETE statements to avoid accidentally deleting data that you don't intend to delete.

Here is other example of delete query.

Delete single row :

DELETE FROM users WHERE id = 1;

Delete multiple row :

DELETE FROM users WHERE id IN (1,2,3,4,5,6,7)

Delete table

To delete an entire table in PostgreSQL, you can use the DROP TABLE statement. The basic syntax for the DROP TABLE statement is:

For example, to delete the users table, you can use the following DROP TABLE statement:

DROP TABLE users;

This will delete the users table and all of the data it contains.

It's important to note that the DROP TABLE statement is irreversible and will permanently delete the table and all of its data. Be careful when using the DROP TABLE statement, as it cannot be undone.

If you want to delete only the data in a table and not the table itself, you can use the TRUNCATE statement instead. The TRUNCATE statement will delete all rows from a table, but it will leave the table structure and permissions intact.

Conclution

In conclusion, the CREATE, INSERT, SELECT, UPDATE, DELETE, and DROP TABLE statements are some of the most commonly used SQL commands in PostgreSQL.

The CREATE statement is used to create new table, the INSERT statement is used to insert data into a table, the SELECT statement is used to retrieve data from a table, the UPDATE statement is used to modify data in a table, the DELETE statement is used to remove data from a table, and the DROP TABLE statement is used to delete a table.

It's important to carefully consider which SQL command is appropriate for each task, as each command has its own specific use cases and consequences. The INSERT, SELECT, UPDATE, and DELETE statements allow you to manipulate data in a table, while the DROP TABLE statement permanently deletes a table and all of its data.

By using these SQL commands effectively, you can effectively manage and manipulate data in a PostgreSQL database.