If you are unfamiliar with SQL and what Postgres commands is available to you. Here is a compilation of Resources
PostgreSQL 14.2 Documentation
This is the documentation you should refer to regarding PostgreSQL
Setup Postgres DB Instance
Go to ElephantSQL
Register an account
Click on Create New Instance
Get Instance Infomation
Copy the following from your ElephantSQL instance
Server
User
Password
Download & Install pgAdmin
Download & Install the application from https://www.pgadmin.org/
Run the Application
Enter the DB Instance information, you can read more from https://www.elephantsql.com/docs/pgadmin.html
Server
User
Password
You can see your session is active.
Because your instance is shared with others. Look for yours.
Right Click > Query Tool
Type in your SQL statements into the editor
Click on the Play Icon to Execute
You will see the result below
Next you will learn all the fundamental of SQL Statements and Data Types sufficient for you to kick start some projects.
Data Types
To understand what are the Data Type you can use for the TABLE, you can use this as a reference.
List of Commonly Used Types
1 | TIMESTAMP | Stores both date and time values. | |
2 | INT | A integer in 4-byte size. | |
3 | VARCHAR | Its a variable-length character string, you can also use TEXT instead. | |
4 | BOOLEAN | Only have two value, TRUE or FALSE. | |
5 | SERIAL | Serial works similar to the integers except these are automatically generated in the columns by PostgreSQL. | |
6 | NUMERIC(x, y) | NUMERIC(Precision, Scale)
Useful for situation like prices etc, NUMERIC(6, 2) | |
You can read more here.
Create Table
Below is an example of how to create table and its columns
CREATE TABLE employees(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address TEXT,
salary NUMERIC(8, 2)
);You can read more here.
Drop Table
Below is an example of how to drop table if it exist.
DROP TABLE IF EXISTS employees;You can read more here.
Using SERIAL for ID
Lets say you want to create a list of fruits, you will need to create a TABLE.
You will likely need an ID for each row in running number. This is when you use SERIAL, usually for the PRIMARY KEY
CREATE TABLE employees(
id SERIAL PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address TEXT,
salary NUMERIC(8, 2)
);You can read more here
What is CRUD?
In computer programming, create, read, update, and delete (CRUD) are the four basic operations of persistent storage.
Get familiar with the command or statement that meant the same.
INSERT Statement
INSERT INTO employees (
name,
age,
address,
salary
)
VALUES
('Tom', 32, '23 Smith St', 999.99),
('James', 26, '39 Sims View', 5000.00),
('Sam', 45, '88 Oxley Rd', 65544.66);You can read more here
SELECT Statement
This is a statement to query data from the database table. The followings can be used with the SELECT statement are listed below:
FROM : It is used to specify a column in a table.
WHERE : It is used to filter rows from a table.
ORDER BY : It is used to sort table rows.
Here is an example how to select all columns and rows from table "employees"
SELECT * FROM employees;Here is an example set condition on the query and return the rows
SELECT *
FROM employees
WHERE age > 30;Here is an example how to sort the rows
SELECT
*
FROM
employees
WHERE
age > 30
ORDER BY
name DESC;Here is an example how to select only the name and salary
SELECT
name, salary
FROM
employees
WHERE
age > 30
ORDER BY
name DESC;You can read more on the following
UPDATE Statement
Here is an example how to update the name where the id is 1
UPDATE employees
SET name = 'May'
WHERE id = 1;You can read more on the following
DELETE Statement
Here is an example how to delete the row where the name is May
DELETE FROM employees
WHERE name = 'May';You can read more on the following
Comments