Search code examples
sqlpostgresqlprepared-statement

PostgreSQL: How to create array with empty string in PreparedStatement?

I have test table with one empty name value:

create table tmp_table_test (id serial, name varchar(100));

insert into tmp_table_test (name) values ('');
insert into tmp_table_test (name) values ('a');
insert into tmp_table_test (name) values ('b');

When I run this query:

SelectSQLPrepared("SELECT id, name FROM tmp_table_test WHERE name IN ('', 'a') order by 1;")

I see two records.

Now I want to change it into PreparedStatement and see the same records, but I don't know how to insert empty string into array.

I tried

SelectSQLPrepared("SELECT id, name FROM tmp_table_test WHERE name IN (SELECT unnest(?::text[])) order by 1;", "{'', a}")

or:

SelectSQLPrepared("SELECT id, name FROM tmp_table_test WHERE name = ANY (?::text[])", "{'', a}")

but it returned only record with a name.

I tried:

SelectSQLPrepared("SELECT id, name FROM tmp_table_test WHERE name IN (SELECT unnest(?::text[])) order by 1;", "{, a}")

but it gives error:

ERROR: malformed array literal: "{, a}"

The only query that I invented looks strange:

SelectSQLPrepared("SELECT id, name FROM tmp_table_test WHERE name IN (SELECT unnest(string_to_array(?, ','))) order by 1;", ",a")

There is ,a as parameter to string_to_array() which gives me array with an empty string and a, but there I do not know how to escape colons which can be in input values.

Is there any solution to use prepared statement with IN clause to search for empty string values?

Solution

  • You can pass an array directly by using the ANY operator (to which the INgets rewritten anyway) and cast it in the prepared statement source:

    SELECT id, name FROM tmp_table_test WHERE name = any(cast ? as text[]) order by 1

    The pass a String literal like {"", a}, e.g.: 

    pstmt.setString(1, "{\"\", a}"