{"id":36152,"date":"2024-07-06T11:12:54","date_gmt":"2024-07-06T01:12:54","guid":{"rendered":"https:\/\/database.guide\/?p=36152"},"modified":"2024-07-06T11:12:55","modified_gmt":"2024-07-06T01:12:55","slug":"using-array_replace-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/using-array_replace-in-postgresql\/","title":{"rendered":"Using ARRAY_REPLACE() in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL we can use the array_replace()<\/code> function to replace a specified element (or elements) in an array with another element.<\/p>\n\n\n\n

The first argument is the array, the second is the element to replace, and the third is the element to replace it with.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s a simple example to demonstrate:<\/p>\n\n\n\n

SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Cat', 'Bird');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{Bird,Dog,Bird,Horse}<\/pre>\n\n\n\n
Here I replaced Cat<\/code> with Bird<\/code>. There were two occurrences and so both occurrences were replaced.<\/p>\n\n\n\n
No Match<\/h2>\n\n\n\n
If there are no matching elements nothing is replaced:<\/p>\n\n\n\n
SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 'Bird');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{Cat,Dog,Cat,Horse}<\/pre>\n\n\n\n
In this case an identical array was returned.<\/p>\n\n\n\n
Wrong Types<\/h2>\n\n\n\n
Here’s what happens when the second argument’s type doesn’t match the array type:<\/p>\n\n\n\n
SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 1, 'Bird');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function array_replace(text[], integer, unknown) does not exist
LINE 1: SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 1, 'B...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
Same with the third argument:<\/p>\n\n\n\n
SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 1);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function array_replace(text[], unknown, integer) does not exist
LINE 1: SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
It is possible to overcome this error by using single quotes:<\/p>\n\n\n\n
SELECT \n    array_replace(ARRAY['Cat','Dog','Cat','Horse'], '1', 'Bird'),\n    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', '1');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
    array_replace    |    array_replace    
---------------------+---------------------
 {Cat,Dog,Cat,Horse} | {Cat,Dog,Cat,Horse}<\/pre>\n\n\n\n
Or we could explicitly cast the integers as text:<\/p>\n\n\n\n
SELECT \n    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 1::text, 'Bird'),\n    array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Bull', 1::text);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
    array_replace    |    array_replace    
---------------------+---------------------
 {Cat,Dog,Cat,Horse} | {Cat,Dog,Cat,Horse}<\/pre>\n\n\n\n
Multi Dimensional Arrays<\/h2>\n\n\n\n
The array_repace()<\/code> function works with multi dimensional arrays:<\/p>\n\n\n\n
SELECT array_replace(ARRAY[ARRAY['Cat','Dog'],ARRAY['Cat','Horse']], 'Cat', 'Bird');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
       array_replace       
---------------------------
 {{Bird,Dog},{Bird,Horse}}<\/pre>\n\n\n\n
Replacing NULL Values<\/h2>\n\n\n\n
We can use array_replace()<\/code> to replace NULL values:<\/p>\n\n\n\n
SELECT array_replace(ARRAY[null,'Dog',null,'Horse'], null, 'Bird');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{Bird,Dog,Bird,Horse}<\/pre>\n\n\n\n
We can also swap it around so that we replace a non-null value with null<\/code>:<\/p>\n\n\n\n
SELECT array_replace(ARRAY['Cat','Dog','Cat','Horse'], 'Cat', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{NULL,Dog,NULL,Horse}<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL we can use the array_replace() function to replace a specified element (or elements) in an array with another element. The first argument is the array, the second is the element to replace, and the third is the element to replace it with.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[169,93,20],"class_list":["post-36152","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-arrays","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36152"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36152\/revisions"}],"predecessor-version":[{"id":36634,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36152\/revisions\/36634"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}