This tutorial will give you quick reference to some command PostgreSQL queries. Like create a table, inserting records, updating records, and many more.
Creating a Database
CREATE DATABASE <Database Name>;
It is a simple query to create a database.
Select The Database
Before executing any of the further queries, we need to select/connect the database. Execute following command the select the database.
\c <Database Name>;
Creating a Table
Once you select the database, we can create a table using following query:
CREATE TABLE <Table Name> (<column_name> <Type>);
For example:
CREATE TABLE students(id SERIAL PRIMARY KEY, student_name VARCHAR(100) NOT NULL, age INT NOT NULL, gender CHAR(6) NOT NULL, address VARCHAR(255));
Above query will create a students table with ID that auto increment. If you look close, we haven't added NOT NULL in address column, so it is not required field and we can skip it.
Inserting records
Now, let's check how we can insert the records.
INSERT INTO <Table Name> (column names) VALUES (Data as per the column names);
We need to mention table name first and than column name. We can write column name where we want to insert the data. You can skip any column that hasn't add NOT NULL in the column.
For example:
INSERT INTO students (student_name, age, gender, address) VALUES ('John Doe', 21, 'Male', '83241 Hodkiewicz Mall, Lake Abel');
INSERT INTO students (student_name, age, gender, address) VALUES ('Jenny Mathew', 23, 'Female');
Likewise, we can insert the data.
Selecting table
If we want to check the records from the table, here is the simple query we can list out all the records from the table.
SELECT * FROM <table name>;
We can add WHERE clause here and retrieve the specific record.
SELECT * from <table name> WHERE id=1;
Updating records
If we want to change the specific record, we can do it with UPDATE keyword.
UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=10;
Here we need to provide table name followed by column name and its value. The WHERE clause is important because we need to mention which record need to update. So WHERE clause can get the record by id column and update the record.
For example:
UPDATE students SET age=23 WHERE id=1;
Deleting record
Now, lets check how we can delete the specific record using WHERE clause.
DELETE FROM <table name> WHERE <column name> = <value>;
Be caution in column name. If you mention column name that has duplicate values, it will delete all the records with that value.
For example:
DELETE FROM students WHERE id=1;
If we want to empty the table or delete all the records from the table, we can do it using following query:
DELETE FROM <table name>;
Dropping table
It's a simple query to delete the table from database.
DROP TABLE <table name>;
Once we execute this query, the table cannot able to recover unless we have backup.
Dropping database
It's simple query as well. Execute following query to delete the database. Once we execute following query, we cannot able to recover it unless we have backup.
DROP DATABASE <database name>;
That's it , we have seen quick reference to some command PostgreSQL queries. Like create a table, inserting records, updating records, and many more.
