Photo of DeepakNess DeepakNess

Useful SQL queries

Unproofread notes

Actually, I have recently set up a PostgreSQL database on a VPS for a new project I am working on.

And since this is the very project of this kind that I am working on, I am learning some basic SQL queries and will be taking a note of some of them below:

Common SQL queries

Here are the most essential PostgreSQL queries you'll likely use frequently:

  1. Select all records:

    SELECT * FROM sample_table;

  2. Create a new table:

    CREATE TABLE sample_table (id SERIAL PRIMARY KEY, name VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

  3. Add a new column:

    ALTER TABLE sample_table ADD COLUMN status VARCHAR(50);

  4. Change column data type:

    ALTER TABLE sample_table ALTER COLUMN rating TYPE FLOAT;

  5. Update specific rows:

    UPDATE sample_table SET status = 'Active' WHERE status = 'Working';

  6. Delete specific rows:

    DELETE FROM sample_table WHERE created_at < '2023-01-01';

  7. Insert new records:

    INSERT INTO sample_table (name, status) VALUES ('Example', 'New');

  8. Rename a column:

    ALTER TABLE sample_table RENAME COLUMN old_name TO new_name;

  9. Drop a column:

    ALTER TABLE sample_table DROP COLUMN unused_column;

  10. Remove all rows from the table while preserving the structure

    TRUNCATE TABLE sample_table RESTART IDENTITY;

Comment via email