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

Question

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?

user330315 · Accepted Answer · 2020-05-22 15:45:43Z

2

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}"

answered May 22, 2020 at 15:45

user330315

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

1 Comment

Michał Niklas Over a year ago

I'm surprised I have to use " instead of "normal" string delimeters: ', but it works. Thank you.

Pavel Stehule · Accepted Answer · 2020-05-22 15:35:27Z

0

There is a array constructor ARRAY

postgres=# SELECT ARRAY[''];
┌───────┐
│ array │
╞═══════╡
│ {""}  │
└───────┘
(1 row)

postgres=# SELECT ARRAY['', 'a'];
┌────────┐
│ array  │
╞════════╡
│ {"",a} │
└────────┘
(1 row)

answered May 22, 2020 at 15:35

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related