How to Copy the Records from One Column in a PostgreSQL Table to Another
Introduction
When you create a table in PostgreSQL and define columns for it, you can’t always predict how requirements may evolve as time goes on. There may be situations where you need to copy data from one column to another within a table. Fortunately, this task is easy to accomplish using the UPDATE
command in SQL. In this tutorial, we’ll show you how to create a SQL statement using UPDATE
that will copy records from a column in a PostgreSQL table and insert them into another column.
Prerequisites
It’s helpful to follow along with the examples we’ll be showing you in this article, so be sure that the following prerequisites are in place:
First, make sure that PostgreSQL is installed. To check if the status is
active
, use theservice postgresql status
command. You can then press CTRL + C to exit.You’ll also need to have
psql
, the command-line interface for PostgreSQL, installed on your machine. Simply typepsql -V
to verify that it’s installed and working properly.
Enter the ‘psql’ Command-line Interface for PostgreSQL
Throughout this tutorial, we’ll be interacting with our PostgreSQL database through psql
. You can use the following command in a terminal window to access a PostgreSQL database using the psql
command-line interface:
1 | psql someUserName -h 127.0.0.1 -d some_database |
You’ll be prompted for the user’s password. Once you input the password and press Return, you should then have access to the database.
Connecting to a Database in ‘psql’
If you entered the psql
interface without specifying a database, or if you just decide that you want to connect to another database instead, you can use the following command:
1 | \c 'database_name' |
Return a List of All PostgreSQL Table Information
Once you’re inside the psql
interface, you can use the \dt
command to list all of the databases. You can also use the \d+
command, followed by a table name, to have psql
return a list of a specific table’s column names:
1 | \d+ some_table |
This command should return a table that includes the Column
and Type
for all the table’s column names and their respective data types.
NOTE: The data type character varying
is equivalent to VARCHAR
.
Create a New PostgreSQL Table Column and Copy Records into It
Now, let’s create the new column into which we’ll be copying records.
Add a column to the PostgreSQL table
We’ll use the ALTER TABLE
and ADD COLUMN
SQL keywords to execute a SQL statement that will add our new column. Here’s an example of a SQL statement that will create a new column for some_table
with a ‘character varying’ data type:
1 2 | ALTER TABLE some_table ADD COLUMN col_NEW VARCHAR (64); |
NOTE: Make sure that your new column’s data type matches the data type of the original column being copied.
You should get a response saying ALTER TABLE
.
Use the ‘UPDATE’ SQL Keyword to Copy the Contents of a PostgreSQL Column
Next, we’ll copy the contents of an existing column into our newly-created column:
1 | UPDATE some_table SET col_NEW = col_str; |
This statement should return a response saying UPDATE
, followed by an integer value indicating the number of records that were copied (e.g. UPDATE 3
).
Getting an Error after Executing the ‘UPDATE’ Statement
If one of the columns specified in the above SQL statement doesn’t exist, you’ll get an error:
1 | ERROR: COLUMN "int_col" does NOT exist |
You may also get an error stating: ERROR: null value in column "old_col" violates not-null constraint
. This could mean that the data type for the new column doesn’t exactly match the old column’s data type. If you encounter this type of error, drop the column and try again.
Return All Record Data for the Table
We can use the SELECT
keyword to have psql
return all record data for the table. This will help us verify that the column of records was successfully copied:
1 | SELECT * FROM some_table; |
You should now see a table of data that includes the new column named col_new
with data copied into it:
Drop and Delete the New PostgreSQL Column
If you decide that you’d like to DROP
a column in your table, you can use the ALTER TABLE
and DROP COLUMN
keywords. Here’s an example that shows how to drop our newly-created column and all of its data:
1 2 | ALTER TABLE some_table DROP COLUMN col_NEW; |
Conclusion
When you need to make changes to a table in your PostgreSQL database, it’s important to know which commands to use for the job. In this tutorial, we focused on the UPDATE
and SET
keywords, which can be used in tandem to copy records from a column in a PostgreSQL table and insert them into another column. Using the examples we provided throughout this article, you’ll be able to copy and move data in your own database environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started