PostgreSQL Docker container is an installation/setup of the PostgreSQL database that is running within a Docker container. In PostgreSQL, a table is a collection of data organized or structured into rows and columns. Each row and each column in a table represent a record and an attribute respectively. Users can easily create tables, update their records and even delete/drop tables in the PostgreSQL Docker container.
This article will demonstrate:
- Prerequisite Steps for Creating, Updating, and Deleting a Table in PostgreSQL Container
- How to Create a Table in PostgreSQL Container?
- How to Update Table in PostgreSQL Container?
- How to Delete Table in PostgreSQL Container?
Prerequisite Steps for Creating, Updating, and Deleting Table in PostgreSQL Container
First, build and execute the Postgres container via the “docker run --name <container-name> -e POSTGRES_PASSWORD=<password> -p 5432:5432 -d postgres” command:
docker run --name postgresCont -e POSTGRES_PASSWORD=pass123 -p 5432:5432 -d postgres
Subsequently, the “postgresCont” PostgreSQL container will be created and started:
Then, type out the below-listed command with the container name to open the shell within it:
docker exec -it postgresCont bash
In the below screenshot, it can be observed that the shell has been opened and we can run/execute commands in it:
After that, establish a connection with the Postgres Database Server with the help of the below-listed command:
psql -h localhost -U postgres
According to the below image, SQL Shell has opened where we can utilize and psql commands and SQL queries:
Now, use the “CREATE DATABASE” command with the desired database name to create it. For example, we created the following database:
CREATE DATABASE tsl_employee;
As you can see, the database has been efficiently created:
Now, connect to the recently created database using the “\c” command:
\c tsl_employee;
It can be seen that the connection has been established:
Let’s move ahead and see how to create, update, and delete tables in the PostgreSQL Docker container.
How to Create a Table in PostgreSQL Container?
To make/create a new table in the particular database, use the “CREATE TABLE <table-name>(col1 <datatype>, col2 <datatype>, col3 <datatype>,....., colN <datatype>);” command. For instance, we have made a table named “tech_authors” with some columns:
CREATE TABLE tech_authors(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, TYPETEXT NOT NULL, CATEGORY TEXT NOT NULL, ARTICLES INT NOT NULL);
Then, utilize the “INSERT INTO <table-name> VALUES (val1, val2, val3, ...);” command for putting new values into the recently created table. Here, we have put the below-provided values:
INSERT INTO tech_authors VALUES (1, 'Laiba', 'Senior', 'Docker', 50);
Now, list table data using below-stated command:
SELECT * FROM tech_authors;
This command has displayed all the records of specified table:
Moreover, if users want to view particular columns of the table, use the “SELECT <col_1>, <col_2>, … <col_N> FROM <table_name>;” and specify the columns names and table name. For instance, we want to only display the “ID”, “NAME” and “TYPE” columns from the selected table:
SELECT ID, NAME, TYPE FROM tech_authors;
The above command has displayed the desired data:
How to Update Table in PostgreSQL Container?
First, display the table data and select the particular record that you want to change or update:
SELECT * FROM tech_authors;
The below output has displayed the table data. Here, we want to change the category of the user whose ID is “1”:
Then, execute the “UPDATE <table-name> SET <column1> = <value1>, <column2> = <value2>, … WHERE condition;” command and specify the column name, value, and conditions. For instance, we have specified the following values and conditions:
UPDATE tech_authors SET CATEGORY= 'Linux' WHERE ID=1;
Next, ensure that the column’s value has been updated using the provided command:
SELECT * FROM tech_authors;
In the below screenshot, the highlighted part shows that the table’s value has been updated successfully:
How to Delete Table in PostgreSQL Container?
In PostgreSQL, users can delete a specific record from the table, delete all records from the table and delete the entire table.
Delete Specific Record from Table
First, display the table data and choose a specific record that needs to be deleted:
SELECT * FROM tech_authors;
Here, we want to delete the record of an entity whose ID is “5”:
Now, execute the following command and specify the ID of the entity whose record you want to delete:
DELETE FROM tech_authors WHERE id=5;
Then, verify whether the specific record has been deleted or not by displaying the table data:
SELECT * FROM tech_authors;
The below output indicates that the record of the specified entity has been deleted:
Delete All Records of Table
Type out the “DELETE FROM <table_name>” command to delete all record of the table:
DELETE FROM tech_authors;
For the verification, view the table data:
SELECT * FROM tech_authors;
In the below output, no record can be seen in the table which indicates that the all records of the table has been deleted successfully:
Delete Entire Table
To delete an entire table, choose the desired table that you want to delete:
\dt
For instance, we want to delete the “tech_authors” table:
Then, execute the “DROP TABLE <table_name>” command to delete the table entirely:
DROP TABLE tech_authors;
Finally, ensure that the table has been deleted by displaying all tables:
\dt
According to the below output, the specified table has been deleted from the database:
That was all about creating, updating, and deleting tables in the PostgreSQL Docker container.
Conclusion
PostgreSQL Docker container enables users to run the PostgreSQL database within a particular Docker container. For this purpose, they need to create and start a PostgreSQL container and set up a connection with the Database Server. After that, they can connect to the particular database and create tables, update their records, and delete tables in it. This article has efficiently demonstrated the method to create, update, and delete tables in the PostgreSQL docker container.
