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:

1
sudo su - postgres

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

1
psql

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:

1
psql -U postgres -d my_db

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

1
psql my_db postgres

Screenshot of using psql to select a database from command line

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:

1
CREATE DATABASE test_db;

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:

1
\CONNECT template

psql: list tables

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

1
\dt

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.

Screenshot of the commands in psql to select a database for PostgreSQL

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:

1
GRANT ALL PRIVILEGES ON DATABASE test_db TO orkb;

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

1
SET ROLE postgres;

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:

1
2
SELECT datname AS "Database Name", datacl AS "Permissions"
FROM pg_database WHERE datname = 'articles';

Screenshot of a permission denied error after using psql to select a database for PostgreSQL

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:

1
2
3
4
5
6
CREATE TABLE new_table (
  id INT NOT NULL PRIMARY KEY,
  col1 VARCHAR(500),
  col2 BOOLEAN,
  col3 JSON
);

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.