How to Use the PostgreSQL Update
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.
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 -Vcan 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:
sudo su - postgres
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
psql -U postgres -d my_db
You can also connect to
psql without using the
-U flags, but you must specify the database name first:
psql my_db postgres
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:
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
\c psql command followed by the database name. The following example tells psql to connect to the built-in
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
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
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:
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:
SELECT datname AS "Database Name", datacl AS "Permissions"
FROM pg_database WHERE datname = 'articles';
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:
CREATE TABLE new_table (
id INT NOT NULL PRIMARY KEY,
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