select array element PostgreSQL

Question

I have a column in my database called: "CIElabOne" which is of the type numeric [] ("CIElabOne" numeric[]) and thus contains values like: {9.766934377517181,0.0011685082518947398,-0.0023119569625251746}

I cant access the values independently, when executing the following SQL query:

SELECT "fileName" FROM "clothItems" WHERE "CIElabOne[1]" = '9.766934377517181'

The result is : ERROR: column "CIElabOne[1]" does not exist

Selecting CIElabOne as a whole is not a problem but I need to evaluate each of the elements of the array. I don't know why this happens I am following the guide http://www.postgresql.org/docs/9.1/static/arrays.html but I don't seem to find the error

This is my real sql query in java:

sqlTwo = "SELECT \"fileName\" FROM \"clothItems\" WHERE \"CIElabOne[1]\" = '"
                    + inputColorOneCIELAB[0]
                    + "' AND \"CIElabOne[2]\" = '"
                    + inputColorOneCIELAB[1]
                    + "' AND \"CIElabOne[3]\" = '"
                    + inputColorOneCIELAB[2]
                    + "' and \"gender\" = '"
                    + inputGender
                    + "' AND \"shape\" <> '"
                    + inputShape
                    + "'";

inputColorOneCIELAB[] is an array of doubles

Could you please show your real Java code? It's unclear what your query really is with all these useless quotes. — JB Nizet
– JB Nizet, Commented Oct 31, 2015 at 10:58
Why do you quote every column name and value? Just use select filename from clothitems where cielabone[1] = ? and .... Use a prepared statement and pass parameters instead of using concatenation. — JB Nizet
– JB Nizet, Commented Oct 31, 2015 at 11:42
I dont think quoting is a problem, I have 5 other sql queries with the cuoting and work perfectly — user3540466
– user3540466, Commented Oct 31, 2015 at 11:53

Craig Ringer · Accepted Answer · 2015-10-31 12:08:39Z

3

The array subscript operator must be outside the quoted identifier name, otherwise it's treated as part of the identifier.

"CIElabOne[1]"

means "the column named CIElabOne[1]." You want:

"CIElabOne"[1]

which means "the first array element of the column CIElabOne.

answered Oct 31, 2015 at 12:08

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

1 Comment

user330315 Over a year ago

@user3540466 Another good reason why quoted identifiers should be avoided

Collectives™ on Stack Overflow

select array element PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related