Postgres String Array Comparison

Question

I have an array of the form :

myArray = ["1234-56", "1234-567"]

My table has a column which is constructed exactly like the array and consists of a string array, we call the column : myColumn.

I want to output the rows where , one or more values of the arrays match.

My current attempt was the following :

SELECT *
FROM myTable
WHERE myColumn && myArray;

But this ends with the following error message:

ERROR: Column "56" does not exist.

It seems you are not passing the array constant properly from within your code. What are you using? Java? C++? C#? Please edit your question and add the code that runs your query. — user330315
– user330315, Commented Jun 21, 2020 at 13:44
The Array comes from JavaScript. The Code that runs my query is above. — sd3v
– sd3v, Commented Jun 21, 2020 at 13:52

Laurenz Albe · Accepted Answer · 2020-06-21 20:21:25Z

2

A string in double quotes is an “identifier” in SQL, that is the name of a table, column, function or other object.

So when you write

SELECT * FROM mytable
WHERE mycolumn && ARRAY["56","95"];

PostgreSQL will identify "56" as a column name (it couldn't be a table in that context), and it complaint that table mytable has no column called 56.

The solution is to mark 56 as a string literal, that is, surround it with single quotes:

SELECT * FROM mytable
WHERE mycolumn && ARRAY['56','95'];

answered Jun 21, 2020 at 20:21

Laurenz Albe

256k21 gold badges307 silver badges382 bronze badges

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

Collectives™ on Stack Overflow

Postgres String Array Comparison

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