Back to Basics: Writing PostgreSQL

As web developers, we often rely on Object-Relational Mapping (ORM) tools for our database needs. While these tools offer us great abstractions, we often lose touch with the basics of writing raw SQL. So, let's take a step back and revisit the fundamentals of PostgreSQL.

Creating a Table

Start by creating a table. In our case, let's create a table named 'books'.

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publish_date DATE
);

Inserting Data

Next, let's insert some data into our table.

INSERT INTO books (title, author, publish_date)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', '1925-04-10');

Reading Data

To read data, we use the SELECT statement. Here's how you can get all the books from the table:

SELECT * FROM books;

Output:

id |         title         |       author        | publish_date
----+-----------------------+---------------------+--------------
 1  | The Great Gatsby     | F. Scott Fitzgerald | 1925-04-10

Updating Data

If you want to change the data in your table, you can use the UPDATE statement. Let's update the publish date of 'The Great Gatsby':

UPDATE books SET publish_date = '1926-04-10' WHERE id = 1;

Deleting Data

Finally, to delete data, we use the DELETE statement. To remove 'The Great Gatsby' from our table:

DELETE FROM books WHERE id = 1;

That's the basics of writing PostgreSQL! Remember, while ORMs are powerful tools, sometimes going back to the basics can get you out of a sticky situation when you don't have anything else.

Happy Coding!