1. Connect to PostgreSQL

psql -U username -d database

2. Create a New Database

CREATE DATABASE mydatabase;

3. Create a New Table

CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  age INTEGER
);

4. Insert Data into a Table

INSERT INTO mytable (name, age) VALUES ('John Doe', 30);

5. Select Data from a Table

SELECT * FROM mytable;

6. Update Data in a Table

UPDATE mytable SET age = 31 WHERE name = 'John Doe';

7. Delete Data from a Table

DELETE FROM mytable WHERE name = 'John Doe';

8. Drop a Table

DROP TABLE mytable;

9. Drop a Database

DROP DATABASE mydatabase;

10. Show Databases

\l

11. Show Tables

\dt

12. Describe a Table

\d mytable

13. Create an Index

CREATE INDEX idx_name ON mytable (name);

14. Drop an Index

DROP INDEX idx_name;

15. Create a View

CREATE VIEW myview AS SELECT * FROM mytable;

16. Drop a View

DROP VIEW myview;

17. Create a Stored Procedure

CREATE OR REPLACE FUNCTION myproc()
RETURNS TRIGGER AS $$
BEGIN
  -- Procedure logic
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

18. Call a Stored Procedure

SELECT myproc();

19. Drop a Stored Procedure

DROP FUNCTION myproc();

20. Create a Function

CREATE OR REPLACE FUNCTION myfunc(x INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN x * 2;
END;
$$ LANGUAGE plpgsql;

21. Call a Function

SELECT myfunc(5);

22. Drop a Function

DROP FUNCTION myfunc(INTEGER);

23. Create a Trigger

CREATE TRIGGER mytrigger
AFTER INSERT ON mytable
FOR EACH ROW
EXECUTE PROCEDURE myproc();

24. Drop a Trigger

DROP TRIGGER mytrigger ON mytable;

25. Create a User

CREATE ROLE myuser WITH PASSWORD 'mypassword';

26. Grant Privileges to a User

GRANT ALL PRIVILEGES ON mydatabase TO myuser;

27. Revoke Privileges from a User

REVOKE ALL PRIVILEGES ON mydatabase FROM myuser;

28. Show User Privileges

\du

29. Change User Password

ALTER ROLE myuser WITH PASSWORD 'newpassword';

30. Drop a User

DROP ROLE myuser;

31. Show PostgreSQL Version

SELECT version();

32. Show PostgreSQL Status

SELECT pg_stat_activity();

33. Show PostgreSQL Variables

SHOW ALL;

34. Set PostgreSQL Variable

SET work_mem = '16MB';

35. Show PostgreSQL Logs

SELECT pg_read_file('pg_log/postgres.log');

36. Backup a Database

pg_dump mydatabase > mydatabase_backup.sql

37. Restore a Database

psql mydatabase < mydatabase_backup.sql

38. Create a Schema

CREATE SCHEMA myschema;

39. Drop a Schema

DROP SCHEMA myschema CASCADE;

40. Show Schemas

\dn

41. Create a Sequence

CREATE SEQUENCE mysequence START 1;

42. Next Value from a Sequence

SELECT nextval('mysequence');

43. Drop a Sequence

DROP SEQUENCE mysequence;

44. Create a Temporary Table

CREATE TEMP TABLE temp_table (id SERIAL PRIMARY KEY, name VARCHAR(255));

45. Insert Data into a Temporary Table

INSERT INTO temp_table (name) VALUES ('Temp User');

46. Select Data from a Temporary Table

SELECT * FROM temp_table;

47. Create a Composite Key

CREATE TABLE mycomposite (
  id SERIAL,
  name VARCHAR(255),
  age INTEGER,
  PRIMARY KEY (id, name)
);

48. Create a Foreign Key

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES mytable(id)
);

49. Show Foreign Keys

SELECT * FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY';

50. Create a Materialized View

CREATE MATERIALIZED VIEW mymaterializedview AS SELECT * FROM mytable;

51. Refresh a Materialized View

REFRESH MATERIALIZED VIEW mymaterializedview;

52. Drop a Materialized View

DROP MATERIALIZED VIEW mymaterializedview;

53. Create a Domain

CREATE DOMAIN mydomain AS VARCHAR(255);

54. Use a Domain in a Table

CREATE TABLE mytable_with_domain (
  id SERIAL PRIMARY KEY,
  name mydomain
);

55. Create a Custom Type

CREATE TYPE mytype AS (
  name VARCHAR(255),
  age INTEGER
);

56. Use a Custom Type in a Table

CREATE TABLE mytable_with_type (
  id SERIAL PRIMARY KEY,
  info mytype
);

57. Create a JSON Column

CREATE TABLE json_table (
  id SERIAL PRIMARY KEY,
  data JSON
);

58. Insert JSON Data

INSERT INTO json_table (data) VALUES ('{"name": "John", "age": 30}');

59. Query JSON Data

SELECT data->>'name' FROM json_table;

60. Create a JSONB Column

CREATE TABLE jsonb_table (
  id SERIAL PRIMARY KEY,
  data JSONB
);

61. Insert JSONB Data

INSERT INTO jsonb_table (data) VALUES ('{"name": "Jane", "age": 25}');

62. Query JSONB Data

SELECT data->>'name' FROM jsonb_table;

63. Create a Full-Text Search Index

CREATE INDEX idx_fts ON mytable USING gin(to_tsvector('english', name));

64. Perform Full-Text Search

SELECT * FROM mytable WHERE to_tsvector('english', name) @@ to_tsquery('John');

65. Create a Check Constraint

CREATE TABLE check_table (
  id SERIAL PRIMARY KEY,
  age INTEGER CHECK (age >= 0)
);

66. Create a Unique Constraint

CREATE TABLE unique_table (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

67. Create a Not Null Constraint

CREATE TABLE not_null_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

68. Create a Default Value

CREATE TABLE default_table (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

69. Use COALESCE Function

SELECT COALESCE(name, 'Unknown') FROM mytable;

70. Use CASE Statement

SELECT name, 
  CASE 
    WHEN age < 18 THEN 'Minor' 
    ELSE 'Adult' 
  END AS age_group 
FROM mytable;

71. Use GROUP BY Clause

SELECT age, COUNT(*) FROM mytable GROUP BY age;

72. Use HAVING Clause

SELECT age, COUNT(*) FROM mytable GROUP BY age HAVING COUNT(*) > 1;

73. Use ORDER BY Clause

SELECT * FROM mytable ORDER BY age DESC;

74. Use LIMIT Clause

SELECT * FROM mytable LIMIT 10;

75. Use OFFSET Clause

SELECT * FROM mytable OFFSET 5 LIMIT 10;

76. Use INNER JOIN

SELECT a.name, b.order_id 
FROM mytable a 
INNER JOIN orders b ON a.id = b.user_id;

77. Use LEFT JOIN

SELECT a.name, b.order_id 
FROM mytable a 
LEFT JOIN orders b ON a.id = b.user_id;

78. Use RIGHT JOIN

SELECT a.name, b.order_id 
FROM mytable a 
RIGHT JOIN orders b ON a.id = b.user_id;

79. Use FULL OUTER JOIN

SELECT a.name, b.order_id 
FROM mytable a 
FULL OUTER JOIN orders b ON a.id = b.user_id;

80. Use UNION Operator

SELECT name FROM mytable 
UNION 
SELECT name FROM another_table;

81. Use INTERSECT Operator

SELECT name FROM mytable 
INTERSECT 
SELECT name FROM another_table;

82. Use EXCEPT Operator

SELECT name FROM mytable 
EXCEPT 
SELECT name FROM another_table;

83. Use Subqueries

SELECT name FROM mytable 
WHERE age > (SELECT AVG(age) FROM mytable);

84. Use Common Table Expressions (CTE)

WITH age_groups AS (
  SELECT age, COUNT(*) as count 
  FROM mytable 
  GROUP BY age
)
SELECT * FROM age_groups;

85. Use Window Functions

SELECT name, 
  ROW_NUMBER() OVER (ORDER BY age) as row_num 
FROM mytable;

86. Use RANK Function

SELECT name, 
  RANK() OVER (ORDER BY age) as rank 
FROM mytable;

87. Use DENSE_RANK Function

SELECT name, 
  DENSE_RANK() OVER (ORDER BY age) as dense_rank 
FROM mytable;

88. Use LAG Function

SELECT name, 
  LAG(age) OVER (ORDER BY age) as previous_age 
FROM mytable;

89. Use LEAD Function

SELECT name, 
  LEAD(age) OVER (ORDER BY age) as next_age 
FROM mytable;

90. Use JSON Functions

SELECT jsonb_set(data, '{name}', '"Jane"') FROM jsonb_table;

91. Use ARRAY Functions

SELECT ARRAY_AGG(name) FROM mytable;

92. Use STRING_AGG Function

SELECT STRING_AGG(name, ', ') FROM mytable;

93. Use TO_CHAR Function

SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM default_table;

94. Use EXTRACT Function

SELECT EXTRACT(YEAR FROM created_at) FROM default_table;

95. Use DATE_TRUNC Function

SELECT DATE_TRUNC('month', created_at) FROM default_table;

96. Use CURRENT_DATE

SELECT CURRENT_DATE;

97. Use CURRENT_TIME

SELECT CURRENT_TIME;

98. Use CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP;

99. Use NOW Function

SELECT NOW();

100. Use AGE Function

SELECT AGE(NOW(), created_at) FROM default_table;