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 |
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.
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'; |
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