Here are the PostgreSQL commands with operations:
Database Operations
CREATE DATABASE: Creates a new database.
SQL
CREATE DATABASE mydatabase;
DROP DATABASE: Deletes a database.
SQL
DROP DATABASE mydatabase;
ALTER DATABASE: Modifies a database.
SQL
ALTER DATABASE mydatabase RENAME TO mynewdatabase;
Table Operations
CREATE TABLE: Creates a new table.
SQL
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL
);
DROP TABLE: Deletes a table.
SQL
DROP TABLE mytable;
ALTER TABLE: Modifies a table.
SQL
ALTER TABLE mytable ADD COLUMN email VARCHAR(100);
TRUNCATE TABLE: Deletes all rows from a table.
SQL
TRUNCATE TABLE mytable;
Index Operations
CREATE INDEX: Creates an index on a table.
SQL
CREATE INDEX idx_name ON mytable (name);
DROP INDEX: Deletes an index.
SQL
DROP INDEX idx_name;
Constraint Operations
PRIMARY KEY: Creates a primary key constraint.
SQL
CREATE TABLE mytable (
id SERIAL PRIMARY KEY
);
FOREIGN KEY: Creates a foreign key constraint.
SQL
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
parent_id INTEGER,
CONSTRAINT fk_parent FOREIGN KEY (parent_id)
REFERENCES parent_table (id)
);
UNIQUE: Creates a unique constraint.
SQL
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
CHECK: Creates a check constraint.
SQL
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
age INTEGER CHECK (age > 18)
);
Data Manipulation Operations
INSERT: Inserts new data into a table.
SQL
INSERT INTO mytable (name, age) VALUES ('John Doe', 30);
UPDATE: Modifies existing data in a table.
SQL
UPDATE mytable SET name = 'Jane Doe' WHERE id = 1;
DELETE: Deletes data from a table.
SQL
DELETE FROM mytable WHERE id = 1;
Query Operations
SELECT: Retrieves data from a table.
SQL
SELECT * FROM mytable;
JOIN: Combines data from multiple tables.
SQL
SELECT * FROM mytable
INNER JOIN parent_table ON mytable.parent_id = parent_table.id;
GROUP BY: Groups data by one or more columns.
SQL
SELECT name, COUNT(*) FROM mytable GROUP BY name;
HAVING: Filters grouped data.
SQL
SELECT name, COUNT() FROM mytable GROUP BY name HAVING COUNT() > 1;
Transaction Operations
BEGIN: Starts a transaction.
SQL
BEGIN;
COMMIT: Commits a transaction.
SQL
COMMIT;
ROLLBACK: Rolls back a transaction.
SQL
ROLLBACK;
Sequence Operations
CREATE SEQUENCE: Creates a new sequence.
SQL
CREATE SEQUENCE mysequence START WITH 1 INCREMENT BY 1;
ALTER SEQUENCE: Modifies a sequence.
SQL
ALTER SEQUENCE mysequence RESTART WITH 10;
DROP SEQUENCE: Deletes a sequence.
SQL
DROP SEQUENCE mysequence;
View Operations
CREATE VIEW: Creates a new view.
SQL
CREATE VIEW myview AS SELECT * FROM mytable;
DROP VIEW: Deletes a view.
SQL
DROP VIEW myview;
Operator Operations
AND: Logical AND operator.
SQL
SELECT * FROM mytable WHERE name = 'John' AND age > 18;
OR: Logical OR operator.
SQL
SELECT * FROM mytable WHERE name = 'John' OR age > 18;
NOT: Logical NOT operator.
SQL
SELECT * FROM mytable WHERE NOT name = 'John';
IN: IN operator.
SQL
SELECT * FROM mytable WHERE name IN ('John', 'Jane');
LIKE: LIKE operator.
SQL
SELECT * FROM mytable WHERE name LIKE '%John%';
BETWEEN: BETWEEN operator.
SQL
SELECT * FROM mytable WHERE age BETWEEN 18 AND 65;
IS NULL: IS NULL operator.
SQL
SELECT * FROM my
Here are more PostgreSQL commands and operations:
Aggregate Functions
AVG: Calculates the average value.
SQL
SELECT AVG(age) FROM mytable;
SUM: Calculates the sum of values.
SQL
SELECT SUM(salary) FROM mytable;
MAX: Returns the maximum value.
SQL
SELECT MAX(age) FROM mytable;
MIN: Returns the minimum value.
SQL
SELECT MIN(age) FROM mytable;
COUNT: Returns the number of rows.
SQL
SELECT COUNT(*) FROM mytable;
String Functions
UPPER: Converts a string to uppercase.
SQL
SELECT UPPER(name) FROM mytable;
LOWER: Converts a string to lowercase.
SQL
SELECT LOWER(name) FROM mytable;
TRIM: Removes leading and trailing spaces.
SQL
SELECT TRIM(name) FROM mytable;
SUBSTRING: Extracts a substring.
SQL
SELECT SUBSTRING(name, 1, 5) FROM mytable;
Date and Time Functions
NOW: Returns the current date and time.
SQL
SELECT NOW();
CURRENT_DATE: Returns the current date.
SQL
SELECT CURRENT_DATE;
CURRENT_TIME: Returns the current time.
SQL
SELECT CURRENT_TIME;
EXTRACT: Extracts a component from a date or time.
SQL
SELECT EXTRACT(YEAR FROM date_column) FROM mytable;
Mathematical Functions
ABS: Returns the absolute value.
SQL
SELECT ABS(-10);
CEIL: Returns the ceiling value.
SQL
SELECT CEIL(10.5);
FLOOR: Returns the floor value.
SQL
SELECT FLOOR(10.5);
ROUND: Rounds a value to a specified precision.
SQL
SELECT ROUND(10.5678, 2);
Window Functions
ROW_NUMBER: Assigns a unique row number.
SQL
SELECT ROW_NUMBER() OVER (ORDER BY name) FROM mytable;
RANK: Assigns a rank based on a specified order.
SQL
SELECT RANK() OVER (ORDER BY salary DESC) FROM mytable;
DENSE_RANK: Assigns a dense rank based on a specified order.
SQL
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) FROM mytable;
Common Table Expressions (CTEs)
WITH: Defines a CTE.
SQL
WITH mycte AS (
SELECT * FROM mytable
)
SELECT * FROM mycte;
Full-Text Search
TO_TSVECTOR: Converts a string to a tsvector.
SQL
SELECT TO_TSVECTOR('hello world');
TO_TSQUERY: Converts a string to a tsquery.
SQL
SELECT TO_TSQUERY('hello & world');
JSON Functions
JSON_BUILD_OBJECT: Creates a JSON object.
SQL
SELECT JSON_BUILD_OBJECT('name', 'John', 'age', 30);
JSON_BUILD_ARRAY: Creates a JSON array.
SQL
SELECT JSON_BUILD_ARRAY('John', 'Jane', 'Bob'); TBD
Top comments (0)