How to Manage a Database and its Table(s) in PostgreSQL?

The previous article explained how to install a PostgreSQL database on Linux. This article will explain the basics of commands in a PostgreSQL database.

Problem

How to manage a database and its table(s) in PostgreSQL?

Solution

Below are the basic commands of PostgreSQL to manage a database and its table(s):

1. Access to the PostgreSQL database

Use the command below to access PostgreSQL:

sudo -u postgres psql

INFO

The use of capital letters in this article is only to distinguish between original commands from PostgreSQL and data from the user. You don’t have to use the capital letters when running these commands, but you can use all lowercase letters.

2. List all databases

Use the command below to list all databases in PostgreSQL:

\l

3. Creating a new database

Use the format below to create a new database:

CREATE DATABASE database_name;

You can see the options for this command on this page. For example, if you want to create a new database called mydb, use the command below;

CREATE DATABASE mydb;

4. Connect to the database

Use the format below to connect to the database:

\c db_name;

For example, if you want to connect to the mydb database, type the following command:

\c mydb;

5. Create a table

Use the format below to create a new table:

CREATE TABLE table_name (name_of_column1 column_data_type1, name_of_column2 column_data_type2, …);

You can see the options for this command on this page. Type the command below to create an employee table:

CREATE TABLE employee (name varchar (100), age int);

Create a table

6. Display all tables

Use the command below to display all the tables:

\dt

7. Display the table structure

Use the format below to display the table structure:

\d table_name;

For example, if you want to display the employee table structure, run the command below:

\d employee;

If you want to display more information about the table, type the command below:

\d+ employee;

8. Add the column

Use the format below to make a column in the table:

ALTER TABLE table_name ADD column column_name type(nnn);

You can see the options for this command on this page. For example, if you want to add to the city column in the employee table, use the command below:

ALTER TABLE employee ADD column city varchar(100);

9. Insert data into the table

Use the format below to enter data in a table:

INSERT INTO table_name (Column1, Column2,…, ColumnN) VALUES (Value1,…ValueN), (Value1,…ValueN);

You can see the options for this command on this page. Type the command below if you want to insert 2 data to the employee table:

INSERT INTO employee (name,age,city) VALUES ('bob',21,'New York'), ('John',22,'Chicago');

INFO

If you want to insert a value in the form of a number, the number does not have to be flanked with an apostrof (‘…’) sign, whereas if it is a character or a combination of characters and numbers, it must be flanked with an apostrof (‘…’).

10. Displays data in a table

Use the format below to display all data in a table:

SELECT option1 FROM table_name option2;

You can see the options for this command on this page. For example, use the command below to display all data in the employee table:

SELECT * from employee;

11. Update data

Use the format below to update data in a table:

UPDATE table_name SET columnX=valueX WHERE columnY=valueY;

You can see the options for this command on this page. For example, if you want to update the age of the employee named Bob, use the command below:

UPDATE employee SET age=23 WHERE name='bob';

12. Delete data

Use the format below to delete one or more rows of a table:

DELETE FROM table_name WHERE column=value;

You can see the options for this command on this page. For example, if you want to delete the data where the user is in Chicago, use the command below:

DELETE FROM employee WHERE city='Chicago';

13. Delete the column

Use the format below to make changes in the table:

ALTER TABLE db_name DROP COLUMN column_name;

If you want to delete the column, for example, city, you can use the following command:

ALTER TABLE employee DROP COLUMN city;

manage a database and its table(s) in PostgreSQL — Delete the column

14. Empty the table

You can delete all data in a table with the format as below:

TRUNCATE table_name;

You can see the options for this command on this page. For example, if you want to delete all the data in the employee table, type the command below:

TRUNCATE employee;

15. Change a table name

You can change the name of the table using the format below:

ALTER TABLE old_table_name RENAME TO new_table_name;

You can see the options for this command on this page. Use the command below if, for example, you want to change the name of the employee table to employees:

ALTER TABLE employee RENAME TO employees;

16. Delete a table

Use the format below to delete a table:

DROP TABLE table_name;

You can see the options for this command on this page. For example, if you want to delete the employee table, use the command below:

DROP TABLE employees;

17. Delete a database

To delete a database, use the format below:

DROP DATABASE database_name;

You can see the options for this command on this page. For example, if you want to delete the mydb database, type the command below:

DROP DATABASE mydb;

17. Quit from the database

To quit from the database, run the command below:

\q

Note

Actually, the basic commands for PostgreSQL and MariaDB are almost the same, with only there is a slight difference between the two databases.

References

hasura.io
postgresql.org
w3schools.com
geeksforgeeks.org

Visited 14 times, 1 visit(s) today