In this article, I will share what I’ve learned about basic knowledge of database and queries with PostgreSQL examples including one of installation processes.
Contents:
- What is database?
-
Work with PostgreSQL
- 1. Install PostgreSQL
- 2. Create Database
- 3. Create tables
- 4. Insert into: How to insert values
- 5. Select: How to get table values
- 6. Alter: How to add another column to an existing table
- 7. Update: How to update existing row values
- 8. Join: How to connect tables
- 9. Delete: How to delete rows
- 10. Drop table: How to delete tables
What is database
A database is an organized collection of structured data stored electronically, typically managed by a DBMS, forming a database system (What is database software?).
There are two main DBMS: Relational databases and NoSQL/Non relational database. I will focus more on relational databases in this article as PostgreSQL is a relational database.
Relational database is consist of tables with columns and rows. Columns hold specific data types and fields store attribute values. Rows represent a collection of related values. Rows can be so called a primary key, an unique identifier and relationships between rows in different tables are established using foreign keys (What is a Relational Database?).
SQL allows us to communicate with relational databases.
**NoSQL/Non relational databases **are databases which store data in a different way from relational databases. There are variety of ways to store data such as document, key-value, wide-column, and graph (What is NoSQL?.
Work with PostgreSQL
1. Install PostgreSQL
Here is a way to download PostgreSQL on MacOS with homebrew.
Preparation
-
(Optional) Download GUI for PostgreSQL: I chose pgAdmin 4 simply because it’s free (What are the best Mac OS X GUIs for PostgreSQL?)
- make sure homebrew is updated: brew update
- confirm that everything is working fine: brew doctor
Installation
- brew install postgresql Check the command to start postgresql service. It was brew services start postgresql@14 in my case.
2. Create Database
Start the service: brew services start postgresql@14
You will see a success message like Successfully started ‘postgresql@14’ (label: homebrew.mxcl.postgresql@14)Create a database: createdb ‘test’
(Optional for GUI) create an user: createuser testuser
(Optional for GUI) open pgAdmin4
(Optional for GUI) Click “Add New Server”
- (Optional for GUI) Input the server name (e.g. localhost)
(Optional for GUI) click connection tab
(Optional for GUI) Input database name, user name, and address
9 (Optional for GUI) You will see Explorer
- Connect to the database: psql 'test' (and \q to exit)
3. Create tables
Make sure you’ve connected the target database before creating tables.
Syntax:
CREATE TABLE table_name (column_1 datatype, ..., column_n datatype);
# Example
CREATE TABLE users (name text, age smallint, birthday date);
You can find various data types provided by PostgreSQL here.
Chapter 8. Data Types
Then you will find the table with GUI, or you can check with \d command with Terminal.
# Command result
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------------
public | users | table | ownername
(1 row)
4. Insert into: How to insert values
Syntax:
INSERT INTO table_name (column_1, ..., column_n) VALUES (value_1, ... value_n);
# Example
INSERT INTO users (name, age, birthday) VALUES ('Hermione', 10, '1979-09-19');
5. Select: How to get table values
Basic syntax:
SELECT column_1, ..., column_n FROM table_name;
# Example1
SELECT name, age, birthday FROM users;
# Result1
name | age | birthday
----------+-----+------------
Hermione | 10 | 1979-09-19
# Example2
SELECT name FROM users;
# Result2
name
----------
Hermione
When you want to select all columns:
SELECT * FROM table_name;
# Example
SELECT * FROM users;
# Result
name | age | birthday
----------+-----+------------
Hermione | 10 | 1979-09-19
You can find some conditional selections:
SQL WHERE Clause
SQL ORDER BY Keyword
Also we have some SQL functions:
SQL COUNT(), AVG() and SUM() Functions
6. Alter: How to add another column to an existing table
Syntax:
ALTER TABLE table_name ADD column_name datatype;
# Example
ALTER TABLE users ADD house text;
# Result
name | age | birthday | house
----------+-----+------------+-------
Hermione | 10 | 1997-09-19 |
Ron | 10 | 1980-03-01 |
7. Update: How to update existing row values
Syntax:
UPDATE table_name SET column_name1 = column_value1 WHERE column_name2 = column_value2;
#Example
UPDATE users SET house = 'Gryffindor' WHERE name = 'Hermione';
# Result
name | age | birthday | house
----------+-----+------------+------------
Ron | 10 | 1980-03-01 |
Hermione | 10 | 1997-09-19 | Gryffindor
8. Join: How to connect tables
Syntax:
SELECT * FROM table1_name JOIN table2_name ON table1_name.primary_key = table1_name.foreign_key
Suppose we have tier table below and want to get users information with rank data:
CREATE TABLE tier (id serial NOT NULL PRIMARY KEY, rank text, name text UNIQUE NOT NULL);
SELECT * FROM tier
id | rank | name
----+------+----------
1 | s | Hermione
2 | s | Ron
3 | s | Harry
4 | a | Draco
Then the SQL query will be:
SELECT name, age, birthday, house, rank FROM users JOIN tier ON users.name = tier.name;
# Result
name | age | birthday | house | id | rank | name
----------+-----+------------+------------+----+------+----------
Hermione | 10 | 1997-09-19 | Gryffindor | 1 | s | Hermione
Ron | 10 | 1980-03-01 | Gryffindor | 2 | s | Ron
Harry | 10 | 1980-07-31 | Gryffindor | 3 | s | Harry
Draco | 10 | 1980-06-05 | Slytherin | 4 | a | Draco
9. Delete : How to delete rows
Syntax:
DELETE FROM table_name WHERE your conditoin;
# Example
DELETE FROM users WHERE name='Voldemort';
10. Drop table: How to delete tables
Syntax:
DROP TABLE table_name;
# Example
DROP TABLE users;
That’s it! Thank you for reading :)
The original article is here
Top comments (0)