Basic Data Manipulation with PostgreSQL

Data manipulation in PostgreSQL involves inserting, querying, updating, and deleting records in tables. Understanding these basic operations is essential for working with databases effectively.

---

Inserting Data into Tables

To insert data into a table, you use the INSERT INTO statement. Here's an example:

INSERT INTO users (username, created_at) VALUES ('john_doe', CURRENT_TIMESTAMP);

This command inserts a new record into the users table with the username 'john_doe' and the current timestamp.

---

Querying Data with SELECT

The SELECT statement is used to query data from tables. For example:

SELECT * FROM users;

This command retrieves all records from the users table.

---

Updating Existing Records

To update existing records, use the UPDATE statement. For example:

UPDATE users SET username = 'jane_doe' WHERE username = 'john_doe';

This command changes the username 'john_doe' to 'jane_doe' in the users table.

---

Deleting Records from Tables

The DELETE statement is used to remove records from a table. For example:

DELETE FROM users WHERE username = 'jane_doe';

This command deletes the record with the username 'jane_doe' from the users table.

---

Using Basic Operators and Functions

PostgreSQL supports a variety of operators and functions for data manipulation. Here are some examples:

• SELECT * FROM users WHERE username LIKE 'john%'; - Finds all usernames starting with 'john'.
• SELECT COUNT(*) FROM users; - Returns the total number of records in the users table.
• SELECT * FROM users WHERE created_at > '2023-01-01'; - Retrieves records created after January 1, 2023.

---

Conclusion

Basic data manipulation in PostgreSQL involves inserting, querying, updating, and deleting records using SQL statements. By mastering these fundamental operations, you can effectively manage data within your PostgreSQL databases and perform various tasks to meet your application's requirements.