postgres cheat sheet

Guri Bee

2 min read

·

16-10-2024

1

0

Share

PostgreSQL Cheat Sheet: A Comprehensive Guide for Database Administrators

PostgreSQL is a powerful and popular open-source relational database system known for its reliability, data integrity, and extensive feature set. This cheat sheet provides a quick reference for common PostgreSQL commands and concepts, helping you navigate the database with confidence.

Getting Started

• Connecting to the database:

  psql -h <hostname> -p <port> -U <username> -d <database_name>
  

  • Example: psql -h localhost -p 5432 -U postgres -d mydatabase

• Listing available databases:

  \l
  

• Exiting psql:

  \q
  

Data Manipulation Language (DML)

• Creating a table:

  CREATE TABLE customers (
      customer_id SERIAL PRIMARY KEY,
      first_name VARCHAR(255),
      last_name VARCHAR(255),
      email VARCHAR(255)
  );
  

  • Explanation:
    • SERIAL PRIMARY KEY automatically generates a unique integer for each customer.
    • VARCHAR(255) defines a string column with a maximum length of 255 characters.

• Inserting data into a table:

  INSERT INTO customers (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]');
  

• Selecting data from a table:

  SELECT * FROM customers;
  

  • Filtering data:

    SELECT * FROM customers WHERE last_name = 'Doe';
    

• Updating data in a table:

  UPDATE customers SET first_name = 'Jane' WHERE customer_id = 1;
  

• Deleting data from a table:

  DELETE FROM customers WHERE customer_id = 1;
  

Data Definition Language (DDL)

• Dropping a table:

  DROP TABLE customers;
  

• Adding a column to a table:

  ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
  

• Removing a column from a table:

  ALTER TABLE customers DROP COLUMN phone;
  

• Renaming a table:

  ALTER TABLE customers RENAME TO clients;
  

Data Querying

• Using WHERE clause:

  SELECT * FROM customers WHERE last_name LIKE 'D%';
  

  • Explanation: LIKE operator performs pattern matching. 'D%' selects all customers with last names starting with 'D'.

• Using ORDER BY clause:

  SELECT * FROM customers ORDER BY last_name ASC; 
  

  • Explanation: ORDER BY sorts the results in ascending order of last name.

• Using LIMIT clause:

  SELECT * FROM customers ORDER BY first_name ASC LIMIT 5;
  

  • Explanation: LIMIT restricts the number of returned rows to 5.

• Using JOIN clause:

  SELECT c.first_name, o.order_date
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id;
  

  • Explanation: JOIN combines data from two or more tables based on a shared column.

Functions and Operators

• Aggregate functions:

  SELECT COUNT(*) FROM customers; 
  SELECT AVG(age) FROM customers;
  

• Date and time functions:

  SELECT CURRENT_DATE;
  SELECT NOW();
  SELECT EXTRACT(YEAR FROM order_date) FROM orders;
  

• String functions:

  SELECT UPPER(first_name) FROM customers; 
  SELECT LOWER(last_name) FROM customers;
  SELECT SUBSTRING(email, 1, INSTR(email, '@') - 1) FROM customers;
  

Additional Tips

• Use comments:

  -- This is a comment
  SELECT * FROM customers;
  

• Use transactions:

  BEGIN;
  -- Insert, update, or delete statements
  COMMIT; -- Save changes
  ROLLBACK; -- Discard changes
  

• Utilize PostgreSQL's extensive documentation: https://www.postgresql.org/docs/

Credits

• The examples and syntax used in this cheat sheet are based on the official PostgreSQL documentation and examples from various GitHub repositories.

This cheat sheet provides a starting point for navigating PostgreSQL. For more in-depth information, consult the official documentation and explore resources like Stack Overflow and PostgreSQL forums. Happy querying!