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.
To start working with
PostgreSQL database you can use interactive query shell
psql or gui tools like
Squelpro or others. But in this article we will go with
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=#
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.
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=#
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 KEYcolumn
idwill 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
usernamewill have string with max length
100if we not specify it will be
255by default, and we are set the default value to empty string.
email VARCHAR(150) NOT NULLcolumn
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 | email@example.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 INTO "public"."users" ("username", "email", "password") VALUES ('ahmadrosid', 'firstname.lastname@example.org', 'ZZx9skKNMaYe') RETURNING *;
Get data from table
SELECT * FROM users;
The result of this query
basic_postgres=# select * from users; id | username | email | password | created_at ----+------------+----------------+--------------+------------------------------ 1 | ahmadrosid | email@example.com | ZZx9skKNMaYe | 2021-09-22 23:37:59.327921+07 (1 row)
UPDATE "public"."users" SET "username" = 'rosid' WHERE "id" = 1;
Delete singgle row :
DELETE FROM users WHERE id = 1;
Delete multiple row :
DELETE FROM users WHERE id IN (1,2,3,4,5,6,7)
DROP TABLE users;