Type cast exception while trying to execute array_append function in PostGres

Question

The function array_append to append an element to array in postgres has the signature like below

Schema   |     Name     | Result data type | Argument data types  |  Type  | Security | Volatility |  Owner   | Language | Source code  |           Description            
------------+--------------+------------------+----------------------+--------+----------+------------+----------+----------+--------------+----------------------------------
 pg_catalog | array_append | anyarray         | anyarray, anyelement | normal | invoker  | immutable  | rdsadmin | internal | array_append | append element onto end of array

When i execute it like below with the prepared statement to remove an array element like below ,

StringBuilder builder = new StringBuilder();
builder.append("update test set tags = array_append(tags, ?) where id = ?")
pstmt = cursor.prepareStatement(builder.toString());
int queryCount = 0;
pstmt.setString(++queryCount, "2181");
pstmt.setString(++queryCount, "123");

I get the following exception

org.postgresql.util.PSQLException: ERROR: function array_remove(text[], character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 60
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:133)

Below is the schema for the table 'Test'

Column     |           Type           |   Modifiers   | Storage  | Stats target | Description 
---------------+--------------------------+---------------+----------+--------------+-------------
 id          | text                     | not null      | extended |              | 
 tags    | text[]                    | not null      | extended |              |

Can someone let me know how to fix this ? However this works when used without PreparedStatement

The built-in array_append function of Postgres does not meet your requirements? postgresql.org/docs/9.1/static/functions-array.html — Philipp Merkle
– Philipp Merkle, Commented Feb 2, 2017 at 12:32
Also this error occurs only when used with prepared statement. If i execute UpdateQuery with string sql statement appending the parameters then it works fine — Aarish Ramesh
– Aarish Ramesh, Commented Feb 2, 2017 at 12:38
What's in your sql variable, or do you mean builder.toString()? — Philipp Merkle
– Philipp Merkle, Commented Feb 2, 2017 at 12:45

Philipp Merkle · Accepted Answer · 2017-02-02 21:01:20Z

3

Try to cast the first parameter to text as defined in your schema:

UPDATE test SET tags = array_append(tags, ?::text) WHERE id = ?

answered Feb 2, 2017 at 21:01

Philipp Merkle

2,6253 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Type cast exception while trying to execute array_append function in PostGres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related