How to Use the PostgreSQL Update

Introduction

When you’re working with data stored in PostgreSQL, there will be times when you may need to modify existing records in your database. It’s important to know how to perform an update statement to handle these modifications correctly. Before you can do a PostgreSQL update to a table, you’ll also need to know how to select the correct database for the task. In this article, we’ll take a closer look at selecting tables in PostgreSQL and review some examples of how it’s done.

Prerequisites

Before proceeding with this tutorial, make sure that the following prerequisites are in place:

• PostgreSQL must be installed and working on your machine. The command psql -V can be used to display the version of PostgreSQL currently installed on your device.

• You’ll need to have some working knowledge of database administration concepts and basic SQL commands.

Connect to psql

In order to execute commands in PostgreSQL, we’ll need to access the psql command-line console.

The command shown below will grant you login privileges as the postgres superuser once you enter the correct password:

After switching to superuser privileges, you can use this command to start the psql interactive terminal:

Once you are connected to the psql interface, you’ll be able to execute queries and perform various database operations in PostgreSQL.

Use psql to connect to a database

You can select a specific database when you access psql, but you’ll need to specify a user name, like postgres, as well. You can do this using the -U option. The following example accesses the psql command line interface and specifies both the postgres admin user and a database called my_db:

You can also connect to psql without using the -d and -U flags, but you must specify the database name first:

Create a database in Postgres

We’ll need a database and table to test out the PostgreSQL update command in our examples. Let’s start by creating a sample database:

NOTE: Your database name should be lowercase and use underscores (_) instead of hyphens, dashes or spaces to separate multiple words.

The command shown above should return a response of CREATE DATABASE if it was successfully created.

psql: select a database

Next, let’s use the \list (or just \l) command to have PostgreSQL list all of the available databases on your server. We’ll select a database from the list returned by the Postgres server and connect to it!

Connect to a PostgreSQL database

To select a database in psql, you just need to execute the \connect or \c psql command followed by the database name. The following example tells psql to connect to the built-in template1 database:

psql: list tables

Once you’re connected to your chosen database, you can use the \dt command to list its respective tables:

This command should return information on your tables in rows stored in another table called “List of relations”.

NOTE: You can use the \dt+ command instead if you’d prefer to have psql return more detailed information on the database’s tables. The \d+ command, followed by a table name, will return more detailed information about one specific table.

PostgreSQL: connect user to a database

If you’re unable to create PostgreSQL data objects, such as tables or schemas, for a particular database, you might need to GRANT privileges to that user using an admin user like postgres.

To do this, exit from psql using the \q command. Then, reconnect to the database as the postgres user or just use the SET ROLE postgres; command.

You can then use the following SQL statement to grant privileges to modify the test_db database to a user called orkb:

An alternative is to connect to another user or role using the SET ROLE keyword as seen in the next example:

NOTE: If you find that you’re not allowed access to the postgres role while connected to psql as another user, just type \q to quit the interface and go back into it using: psql -U postgres -d new_db.

You can use the following SQL command to have psql return permission information for a Postgres database:

At this point, you should be able to create a table within your Postgres database. Here’s an example of how to create a table:

Conclusion

Being able to perform a PostgreSQL update is important when you’re managing data stored in a database. In order to access a specific table and modify its data, you’ll also need to know how to select a table. In this article, we reviewed the process of selecting a table in PostgreSQL and looked at a few examples. With these instructions and examples, you’ll be able to navigate your own PostgreSQL database environment with ease.