How to Perform the PostgreSQL to Update an Array in Column

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The PostgreSQL update array column allows DBAs to replace old array values with new ones. The best thing about using this statement is that implements the array constructor is that you can overwrite a single value within a record without having to change every other value within an array column. For example, if you have a contact with two phone numbers and one is outdated, you can replace the old one without deleting the other valid phone number within that same record. The benefit is that you perform with higher accuracy and speed when updating records. Let’s go ahead and proceed in illustrating how to accomplish this in the simplest way.

Prerequisites

  • PostgresSQL – Install and configure the object-relational database management system (ORDMS) for your OS.

Create a database and a table in PostgreSQL

  • Construct a sample database, and then make a table to use with the examples in this PostgreSQL update array column tutorial like this:
1
2
3
4
5
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR,
contact TEXT[]
);

Insert value in the table in PostgreSQL

  • Add data for your table like this:
1
2
3
INSERT INTO employees (name, contact)
VALUES ('Kirsten Whitfield', '{"209-200-9063", "209-201-0138"}'),
('Homer Rogers', '{"209-204-4807", "209-202-6774", "209-214-4857"}');

Modifying array values

  • Use brackets [] to indicate which value you want to modify in an array column like this:
1
UPDATE employees SET contact[1] = '209-223-6744' WHERE id = '1';
  • You just updated the contact’s value shown in the first position in the array column for that record. The contact’s phone number in that first ([1]) position will change the value to the new one shown in the SQL statement.

  • Next, show the results by using the SELECT * FROM employees command.

  • Your response should look like this:

1
2
3
4
5
 id |       name        |                  contact
----+-------------------+-------------------------------------------
  2 | Homer Rogers      | {209-204-4807,209-202-6774,209-214-4857}
  1 | Kirsten Whitfield | {209-223-6744,209-201-0138}
(2 ROWS)

>NOTE: The change was made on the record “1” because you assigned [1] as the value in the SQL statement you made earlier. As a result, you replaced the first phone number successfully. The remaining values in the array were undisturbed during the PostgreSQL update array column action.

  • Now try a different example. Here, you’ll perform an array column update without indicating the exact location of the array. Watch what happens.
1
2
3
UPDATE employees SET contact[] = '209-256-6897' WHERE id = '2';
ERROR:  syntax error at OR near "]"
LINE 1: UPDATE employees SET contact[] = '209-256-6897' WHERE id = '...

It didn’t work because the array’s position wasn’t specified in the PostgreSQL update array column statement. It proved that brackets alone won’t work if there isn’t a value inside of them. Therefore, an array must have a value identified in the SQL statement to complete a modification.

  • To correctly change the array column and forgo using brackets, assign a value to the array like this:
1
2
3
UPDATE employees
SET contact = '{"209-240-9984", "209-256-6897"}'
WHERE id = '2';

Great! Since you indicated (2) two values for modification in the PostgreSQL update array column statement, the array in the column did perform the update, changing the data from three values to two values as planned.

  • Use the SELECT * FROM employees command again to see the response. It should look like this:
1
2
3
4
5
6
SELECT * FROM employees;
 id |       name        |           contact
----+-------------------+-----------------------------
  1 | Kirsten Whitfield | {209-223-6744,209-201-0138}
  2 | Homer Rogers      | {209-240-9984,209-256-6897}
(2 ROWS)

Conclusion

The PostgreSQL update array column is useful for seamlessly updating records. Speed and accuracy matter when there are large amounts of data to process. In particular, contact phone numbers change continually. Prospects, customers, and employees might switch area codes when they relocate in and out of the city or state. In other cases, they may drop phone numbers or simply obtain additional ones as they see fit.

It’s important to keep updated records all of the time because a company’s greatest advantage to the competition is its contacts. This includes the data for getting in touch with its people, potential clients, and existing customers. PostgreSQL’s update array column statement helps DBAs keep on top of these resources most productively.

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.