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!