If you’re updating arrays in PostgreSQL and you’ve suddenly realised that some of the arrays aren’t being updated, it could be due to the following.<\/p>\n\n\n\n
If you’ve been using the This could be due to a little “gotcha” with regards to the syntax of these two functions. Basically these two functions accept their arguments in the opposite order to each other. So if you’re switching between the two functions, you’ll need to switch your arguments too.<\/p>\n\n\n\n Here’s a simple example that demonstrates the issue.<\/p>\n\n\n\n Suppose we run the following statement:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n That worked fine. We used But now let’s change Result:<\/p>\n\n\n\n We get an error.<\/p>\n\n\n\n The error message may differ, depending on the data in the array. For example, here’s another error message:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Either way, it’s the same issue – we’re using the wrong syntax for the To fix this problem, all we need to do is swap the arguments around. So that the new value is the first argument:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Same with the other example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n If you’re updating arrays in PostgreSQL and you’ve suddenly realised that some of the arrays aren’t being updated, it could be due to the following. If you’ve been using the array_append() function and the array_prepend() function, you may have found that one function works but the other doesn’t. For example array_append() works but array_prepend() doesn’t, … Read more<\/a><\/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,10],"class_list":["post-36029","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-arrays","tag-how-to"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36029","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=36029"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36029\/revisions"}],"predecessor-version":[{"id":36639,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36029\/revisions\/36639"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}array_append()<\/code> function and the array_prepend()<\/code> function, you may have found that one function works but the other doesn’t. For example array_append()<\/code> works but array_prepend()<\/code> doesn’t, or vice-versa.<\/p>\n\n\n\n\n\n\n\nExample of the Problem<\/h2>\n\n\n\n
SELECT array_append(ARRAY[ 'Cat', 'Dog' ], 'Rabbit');<\/code><\/pre>\n\n\n\n{Cat,Dog,Rabbit}<\/pre>\n\n\n\narray_append()<\/code> to append 3<\/code> to the array.<\/p>\n\n\n\narray_append()<\/code> to array_prepend()<\/code>:<\/p>\n\n\n\nSELECT array_prepend(ARRAY[ 'Cat', 'Dog' ], 'Rabbit');<\/code><\/pre>\n\n\n\nERROR: could not find array type for data type text[]<\/pre>\n\n\n\n
SELECT array_prepend(ARRAY[ 1, 2 ], 0);<\/code><\/pre>\n\n\n\nERROR: function array_prepend(integer[], integer) does not exist
LINE 1: SELECT array_prepend(ARRAY[ 1, 2 ], 0);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\narray_prepend()<\/code> function. Our arguments are in the wrong order.<\/p>\n\n\n\nSolution<\/h2>\n\n\n\n
SELECT array_prepend('Rabbit', ARRAY[ 'Cat', 'Dog' ]);<\/code><\/pre>\n\n\n\n{Rabbit,Cat,Dog}<\/pre>\n\n\n\nSELECT array_prepend(0, ARRAY[ 1, 2 ]);<\/code><\/pre>\n\n\n\n{0,1,2}<\/pre>\n","protected":false},"excerpt":{"rendered":"