A PostgreSQL Docker container is a 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 column in a table represent a record and an attribute respectively.

Users can easily create tables, update their records, and even delete/drop tables from the PostgreSQL Docker container.

Quick Outline

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 PostgreSQL container via the following “docker run” 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

From the screenshot, it can be observed that the shell has been opened and we can run/execute SQL 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/run 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 a 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 the below-stated command:

SELECT * FROM tech_authors;

This command has displayed all the records of the 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 retrieved the desired data from the selected columns:

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”:

Let's 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 From PostgreSQL Container

In PostgreSQL, users can delete a specific record from the table, delete all records from the table, or delete the entire table. Let's see each scenario one by one:

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 records 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 all records of the table have 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

A PostgreSQL Docker container enables users to run the PostgreSQL database within a particular container. For this purpose, they need to create and start a PostgreSQL container and set up a connection with the Database Server.

After establishing a connection, they can connect to the particular database and create tables, update their records, and delete tables from it. This article has efficiently demonstrated the method to create, update, and delete tables in the PostgreSQL docker container.