PostgreSql Commands Note

Posted on: August 16, 2023

This is to learn about SQL - Structured Query Language, the language used to manage relational database Note: All commands need a semi-colon at the end

Login

  • Bash command: psql --username=<username> --dbname=<database_name>
  • To run one single command and exit, add a -c to the end of the above bash command

Database

List all datatbases

  • \l - list

Create database

  • CREATE DATABASE <database_name>;
  • Create table with predefined column: CREATE TABLE <table_name>(<column_name> <DATATYPE> <CONSTRAINTS>);
PSQL
CREATE TABLE sounds(sound_id SERIAL PRIMARY KEY);

Rename database

  • ALTER DATABASE <database_name> RENAME TO <new_database_name>;

Connect to a database

  • \c <database_name>

Display tables in a database

  • \d - display

Table

Create table in a database

  • CREATE TABLE <table_name>();

Remove table

  • DROP TABLE <table_name>

Display details about a table

  • \d <table_name>

Column

Add column to a table

  • ALTER TABLE <table_name> ADD COLUMN <column_name> <DATATYPE>;
PSQL
// E.g.
ALTER TABLE second_table ADD COLUMN first_column INT;
  • ALTER TABLE <table_name> ADD COLUMN <column_name> <DATATYPE> <CONSTRAINTS>;
PSQL
// E.g.
ALTER TABLE second_table ADD COLUMN name VARCHAR(30) NOT NULL;

Remove column

  • ALTER TABLE <table_name> DROP COLUMN <column_name>;

Rename column

  • ALTER TABLE <table_name> RENAME COLUMN <column_name> TO <new_name>;

Row / Data

Insert row

  • INSERT INTO <table_name>(<column_1>, <column_2>) VALUES(<value1>, <value2>);
PSQL
INSERT INTO second_table(id, username) VALUES(1, 'Samus');
// Since username column expects a VARCHAR -> need to put its value into single quote '' not ""
  • Insert multiple rows:
PSQL
INSERT INTO characters(name, homeland, favorite_color)
VALUES('Mario', 'Mushroom Kingdom', 'Red'),
('Luigi', 'Mushroom Kingdom', 'Green'),
('Peach', 'Mushroom Kingdom', 'Pink');

Delete a row

  • DELETE FROM table_name WHERE condition;
PSQL
DELETE FROM second_table WHERE username='Luigi'; 
// This remove the whole row where has the username = Luigi

Delete all data in table

  • In a single table: TRUNCATE <table_name>;
  • In multiple tables: TRUNCATE <table1>, <table2>;

View data in a table

  • View data in a single column: SELECT <column> FROM <table_name>;
  • View data in multiple columns: SELECT <column1>, <column2> FROM <table_name>;
  • View all columns: SELECT * FROM <table_name>;
  • View data under a condition: SELECT <columns> FROM <table_name> WHERE <condition>;
  • View data matches a pattern: SELECT <columns> FROM <table_name> WHERE <column> LIKE <pattern>;
  • View data does not match a pattern: SELECT <columns> FROM <table_name> WHERE <column> NOT LIKE <pattern>;
  • View data match a pattern and ignoring case: SELECT <columns> FROM <table_name> WHERE <column> ILIKE <pattern>;
PSQL
SELECT * FROM courses WHERE course ILIKE '%A%';
// Returns courses that contains 'a' or 'A' in their course name
  • View data does not match a pattern and ignoring case: SELECT <columns> FROM <table_name> WHERE <column> NOT ILIKE <pattern>;
  • View data with empty field: SELECT <column> FROM <table_name> WHERE <column> IS NULL;
  • View data with no empty field: SELECT <column> FROM <table_name> WHERE <column> IS NOT NULL;
  • View the mathematical value with numerical columns: SELECT <function>(<column>) FROM <table_name>;

Change data value

  • UPDATE <table_name> SET <column_name>=<new_value> WHERE <condition>;
PSQL
UPDATE characters SET favorite_color='Orange' WHERE name='Daisy';

Order data

  • SELECT <columns> FROM <table_name> ORDER BY <column_name>;
PSQL
SELECT * FROM characters ORDER BY character_id;
  • Ascending order is default: SELECT <columns> FROM <table_name> ORDER BY <column_name> ASC;
  • Descending order: SELECT <columns> FROM <table_name> ORDER BY <column_name> DESC;
  • Order by multiple columns, any matching values in the first ordered column will then be ordered by the next: SELECT <columns> FROM <table> ORDER BY <column_1>, <column_2>;

Group data

  • Only view unique data: SELECT DISTINCT(<column>) FROM <table>;
  • View data by groups: SELECT <column> FROM <table> GROUP BY <column>;
PSQL
// Can show unique values:
SELECT major_id FROM students GROUP BY major_id;


OR 


// Can add any of the aggregate functions (MIN, MAX, COUNT, etc):
SELECT COUNT(*) FROM students GROUP BY major_id;
  • View group data under a certain condition that is a aggregate function with a test: SELECT <column> FROM <table> GROUP BY <column> HAVING <condition>;
psql
SELECT major_id, MIN(gpa), MAX(gpa) FROM students GROUP BY major_id HAVING MAX(gpa) = 4.0;
// Show the unique major_id, the min and max gpa in each of the major_id group that has a max gpa of 4.0

Limit to return a certain number of rows

  • View the first n rows: SELECT <columns> FROM <table_name> LIMIT <number>;

Change display column name

  • Rename display column: SELECT <column> AS <new_column_name> FROM <table>;

Datatypes

  • INT - integer
  • VARCHAR(n) - short string of characters with max length of n; requires '' for its value
  • SERIAL - make your column an INT with a NOT NULL constraint, and automatically increment the integer when a new row is added
  • NUMERIC(n1, n2) - has up to n1 digits, and has n2 of these digits on the right of the decimal point
  • Date - its value requires single quote and the format is 'YYYY-MM-DD'

Conditions

Comparison Operators

  • =: equals
  • !=: not equals
  • >: greater than, or get elements that come after RHS alphabetically
PSQL
SELECT * FROM alphabet WHERE letter > 'W';
// Returns 'X', 'Y', 'Z'
  • >=: greater than or equals, or get elements that come after RHS alphabetically including the RHS element
PSQL
SELECT * FROM alphabet WHERE letter > 'W';
// Returns 'W', 'X', 'Y', 'Z'
  • <: less than, or get elements that come before RHS alphabetically
  • <=: less than or equals, or get elements that come before RHS alphabetically including the RHS element

Conjunctive Operators

  • AND: need to satisfy both conditions
PSQL
SELECT * FROM digits WHERE digit < 2 AND digit > 5;
// Return 3, 4
  • OR: need to satisfy either one Conditions
PSQL
SELECT * FROM digits WHERE digit < 1 OR digit > 7;
// Return 0, 8, 9

Patterns

  • _: return rows that have any character and exactly one character in that spot
PSQL
SELECT * FROM courses WHERE course LIKE '_lgorithms';
// Return rows that have a course name ends with "lgorithms", such as "Algorithms", but not "Data Structures and Algorithms"
  • %: return rows that can have anything in that spot, unlimited characters
PSQL
SELECT * FROM courses WHERE course LIKE '%lgorithms';
// Can return rows that have a course name "Algorithms" and "Data Structures and Algorithms"

Aggregate Functions

  • Find the lowest value: SELECT MIN(<column>) FROM <table>;
  • Find the largest value: SELECT MAX(<column>) FROM <table>;
  • Find the sum of all values in that column: SELECT SUM(<column>) FROM <table>;
  • Find the average of all values in that column: SELECT AVG(<column>) FROM <table>;
  • Round decimal up to nearest whole number: CEIL(<number_to_round>)
  • Round decimal down: FLOOR(<number_to_round>)
  • Round decimal to nearest whole number: ROUND(<number_to_round>)
  • Round decimal to a specific number of decimal places: ROUND(<number_to_round>, <decimals_places>)
  • Round decimal to a specific number of decimal places: ROUND(<number_to_round>, <decimals_places>)
  • Count how many entries in the table for the column: SELECT COUNT(<column>) FROM <table>;

Constraints

Add primary key

  • A primary key makes a column uniquely identifies each row in the table
  • ALTER TABLE <table_name> ADD PRIMARY KEY(<existing_column_name>);
PSQL
ALTER TABLE characters ADD PRIMARY KEY(character_id);
  • Create a composite primary key using two columns: ALTER TABLE <table_name> ADD PRIMARY KEY(<column1>, <column2>);, this still makes a row unique because there can be multiple rows have the same column1 data in the table and multiple rows have the same column2 data but will never have a row with the same column1 and column2 data.

Add foreign key

  • A foreign key helps to relate rows from a table to rows from another table
  • ALTER TABLE <table_name> ADD COLUMN <column_name> <DATATYPE> REFERENCES <referenced_table_name>(<referenced_column_name>);
  • If column already exists: ALTER TABLE <table_name> ADD FOREIGN KEY(<column_name>) REFERENCES <referenced_table>(<referenced_column>);

Unique constraint

  • Enforce one-to-one relationship between tables, i.e. one row in tableA only relates to exactly one row in tableB and vice versa.
  • ALTER TABLE <table_name> ADD UNIQUE(<column_name>);

Set not null

  • ALTER TABLE <table_name> ALTER COLUMN <column_name> SET NOT NULL;

Remove constraint

  • ALTER TABLE table_name DROP CONSTRAINT constraint_name;
PSQL
ALTER TABLE characters DROP CONSTRAINT characters_pkey;
// This removes the primary key of characters table

Join

Full Join

  • To view all data in two tables: SELECT <columns> FROM <table_1> FULL JOIN <table_2> ON <table_1>.<primary_key_column> = <table_2>.<foreign_key_column>;
PSQL
SELECT character_id FROM characters FULL JOIN more_info ON characters.character_id = more_info.character_id;
  • To view all data in multiple tables:
PSQL
SELECT <columns> FROM <junction_table>
FULL JOIN <table_1> ON <junction_table>.<foreign_key_column> = <table_1>.<primary_key_column>
FULL JOIN <table_2> ON <junction_table>.<foreign_key_column> = <table_2>.<primary_key_column>;


E.g. 
SELECT * FROM character_actions 
FULL JOIN characters ON character_actions.character_id = characters.character_id 
FULL JOIN actions ON character_actions.action_id = actions.action_id;

Left Join

Keywords order

The order of keywords in the query matters.

  1. WHERE
  2. ORDER BY
  3. LIMIT