0

Quite frustrating that array_replace function exists in version 9, but not in 11 or 12. Is there a way to activate this function or something of that sort? Please help

I am running Postgres 11.5, select version() gives me the following

version
------------------------------------------------------------
 PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit

When I list the functions using "\df" array_replace() is NOT one of the few array based functions listed

    List of functions
 Schema |         Name          | Result data type |        Argument data types         | Type
--------+-----------------------+------------------+------------------------------------+------
 public | armor                 | text             | bytea                              | func
 public | armor                 | text             | bytea, text[], text[]              | func
 public | array_append_distinct | anyarray         | anyarray, anyelement               | func
 public | array_distinct        | anyarray         | anyarray                           | func
 public | array_sort            | anyarray         | anyarray                           | func
 public | citext                | citext           | boolean                            | func
 public | citext                | citext           | character                          | func
 public | citext                | citext           | inet                               | func
Mav S.
  • 21
  • 5
  • 2
    What are you talking about? `array_replace()` is present in the documentation of 11 and 12, so I'm quite certain it is there. You better provide a [example] that shows exactly what you want to do and how it fails. – sticky bit Jul 11 '20 at 15:09
  • @stickybit When I run "select array_replace(col, val1, val2) from mytable where id=1;" , I get the following error "No function matches the given name and argument types". When I list the functions using "\df" array_replace() is NOT one of the few array based functions listed – Mav S. Jul 11 '20 at 15:25
  • 1
    Works just fine in Postgres 12: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=c3c35baa1c48fbec443a803bb29e0cfa please **[edit]** your question and add the complete code you are running and the **complete** error message you get as [formatted text](https://meta.stackoverflow.com/a/251362) - [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) please –  Jul 11 '20 at 15:26
  • It means the function is being called with incorrect argument types. What are the actual values for col, val1, val2? What if you do \df array_replace? – Adrian Klaver Jul 11 '20 at 15:29
  • @AdrianKlaver Actually the arguments are fine, the array_replace() function is not listed when I run `\df ` I have edited the question, please view. – Mav S. Jul 11 '20 at 15:33
  • 1
    Yeah, but that is showing functions in the public schema which is not where the system functions exist. They are in schema pg_catalog. What does ```SHOW search_path;``` return? What does ```\df pg_catalog.array_replace``` return? – Adrian Klaver Jul 11 '20 at 15:37
  • Also works fine in 11: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2f21aa61cc46eb68b288b758380a3f5f And you still didn't provide the [example]... – sticky bit Jul 11 '20 at 15:41
  • @AdrianKlaver Thanks Adrian, now I've found that the function exists, I need to figure my arguments and type casts. Thank you. – Mav S. Jul 11 '20 at 15:42

0 Answers0