Installing PostgreSQL on Debian 10

PostgreSQL is a very popular open source Relational Database Management System (RDBMS). In this article, I am going to show you how to install PostgreSQL on Debian 10 and how to use PostgreSQL on Debian 10. So, let’s get started.

Installing PostgreSQL:

The latest stable version of PostgreSQL is available in the official package repository of Debian 10 Buster. So, you can easily install PostgreSQL using the APT package manager.

First, update the APT package repository cache with the following command:

$ sudo apt update

The APT package repository cache should be updated.

Now, install PostgreSQL with the following command:

$ sudo apt install postgresql

Press Y and then press <Enter> to confirm the installation.

The APT package manager will download and install all the required packages.

At this point, PostgreSQL should be installed.

Now, print the version number of PostgreSQL with the following command:

$ psql --version

As you can see, I am running PostgreSQL 11.5. It is the latest stable release of PostgreSQL at the time of this writing.

Managing PostgreSQL Service:

Once PostgreSQL is installed, check whether the postgresql service is running with the following command:

$ sudo systemctl status postgresql

As you can see, the postgresql service is active. The service is also enable. So, it will start automatically on system boot.

If the postgresql service is not active in your case, you can start the postgresql server with the following command:

$ sudo systemctl start postgresql

If the postgresql service is disabled, then it won’t start automatically on system boot. You can enable it with the following command:

$ sudo systemctl enable postgresql

Setting Up PostgreSQL Database User:

With the default configuration, you can use postgresql as only postgres user. So, you have to login as postgres user in order to execute any PostgreSQL command.

The postgres user don’t have any password set by default. So, you won’t be able to login as the postgres user.

To set a password for the postgres user, run the following command:

$ sudo passwd postgres

Now, type in a password and press <Enter>.

Re-type the same password and press <Enter> again.

A password should be set for the postgres user.

Now, to login as the postgres user, run the following command:

$ su - postgres

Type in the password that you’ve set for the postgres user and press <Enter>.

You should be logged in.

Creating a PostgreSQL Database:

Now, to create a new database linuxhint, run the following command:

$ created linuxhint

Once the linuxhint database is created, login to the PostgreSQL shell with the following command:

$ psql linuxhint

You should be logged in to the PostgreSQL shell. Now, you can run necessary SQL statements and work with the linuxhint database.

As you can see, I ran a simple SQL statement and it worked correctly.

SELECT current_date;

Once you’re done working with the database, you can exit out of the PostgreSQL shell as follows:

# \q

You should be back to the Linux shell.

Creating a PostgreSQL Table:

Now, let’s create a new table shops in our linuxhint database.

Here is the SQL statement to create the table shops. Copy and paste the SQL statements in the PostgreSQL shell to create a new table shops.

CREATE TABLE shops (
shopId INT NOT NULL,
shopName VARCHAR(100) NOT NULL,
numEmployee INT NOT NULL
);

The shops table should be created.

You can list all the available tables in your database as follows:

# \d

As you can see, the shops table is in the list.

You can also see the schema of the shops table as follows.

# \d shops

Inserting Data into Table:

Now, let’s insert some new rows into the shops table.

To insert new rows into the shops table, you can run the following INSERT SQL statements.

INSERT INTO shops VALUES(1, 'Food King', 5);
INSERT INTO shops VALUES(2, 'FoodEx', 4);
INSERT INTO shops VALUES(3, 'Dummy Burger', 10);

The SQL statements ran successfully. I’ve inserted 3 dummy rows into the shops table. Great!

Selecting Data from Table:

Now, to select all the rows from the shops table and print them on the console, run the following SQL statement.

SELECT * FROM shops;

As you can see, the 3 dummy rows I’ve inserted earlier is printed on the console in tabular format.

Deleting Data from Table:

To delete a row from the shops table with the shopId 1 (let’s say), run the following SQL statement:

DELETE FROM shops WHERE shopId=1;

The row should be deleted from the shops table.

As you can see, the row is not in the shops table anymore.

SELECT * FROM shops;

Updating Table Data:

Let’s say, you want to update the numEmployee column from 4 to 8 (let’s say) for the shop with the shopId 2 of the shops table.

To do that, run the following SQL statement.

UPDATE shops SET numEmployee=8 WHERE shopId=2;

The row should be updated.

As you can see, the numEmployee for the shop with the shopId 2 is updated to 8.

Removing Table:

To remove the table shops, run the following SQL statement:

DROP TABLE shops;

The table should be removed.

Deleting Database:

To delete the database linuxhint, run the following command as postgres user.

$ dropdb linuxhint

The database linuxhint should be removed.

So, this is how you install and use PostgreSQL on Debian 10. Thanks for reading this article.

Installing PostgreSQL on Debian 10