Manipulate data in PostgreSQL¶
On the previous step, you have connected to PostgreSQL as the superuser postgres. Now, let’s insert some sample data and operate with it in PostgreSQL.
Create a database¶
Let’s create the database test. Use the CREATE DATABASE command:
CREATE DATABASE test;
Create a table¶
Let’s create a sample table Customers in the test database using the following command:
CREATE TABLE customers (
id SERIAL PRIMARY KEY, -- 'id' is an auto-incrementing integer
first_name VARCHAR(50), -- 'first_name' is a string with a maximum length of 50 characters
last_name VARCHAR(50), -- 'last_name' is a string with a maximum length of 50 characters
email VARCHAR(100) -- 'email' is a string with a maximum length of 100 characters
);
Hint:Having issues with table creation? Check our Troubleshooting guide
Insert the data¶
Populate the table with the sample data as follows:
INSERT INTO customers (first_name, last_name, email)
VALUES
('John', 'Doe', '[email protected]'), -- Insert a new row
('Jane', 'Doe', '[email protected]'), -- Insert another new row
('Alice', 'Smith', '[email protected]');
Query data¶
Let’s verify the data insertion by querying it:
SELECT * FROM customers;
Expected output
id | first_name | last_name | email
----+------------+-----------+-------------------------
1 | John | Doe | john.doe@example.com
2 | Jane | Doe | jane.doe@example.com
3 | Alice | Smith | alice.smith@example.com
(3 rows)
Update data¶
Let’s update John Doe’s record with a new email address.
-
Use the UPDATE command for that:
UPDATE customers SET email = '[email protected]' WHERE first_name = 'John' AND last_name = 'Doe'; -
Query the table to verify the updated data:
SELECT * FROM customers WHERE first_name = 'John' AND last_name = 'Doe';Expected output
id | first_name | last_name | email ----+------------+-----------+------------------------- 2 | Jane | Doe | jane.doe@example.com 3 | Alice | Smith | alice.smith@example.com 1 | John | Doe | john.doe@myemail.com (3 rows)
Delete data¶
Use the DELETE command to delete rows. For example, delete the record of Alice Smith:
DELETE FROM Customers WHERE first_name = 'Alice' AND last_name = 'Smith';
If you wish to delete the whole table, use the DROP TABLE command instead as follows:
DROP TABLE customers;
To delete the whole database, use the DROP DATABASE command:
DROP DATABASE test;
Congratulations! You have used basic create, read, update and delete (CRUD) operations to manipulate data in Percona Distribution for PostgreSQL. To deepen your knowledge, see the data manipulation section in PostgreSQL documentation.