1

I have a Postgres table containing a column of type text[][]. In JDBC code I've used a String array, but an exception told me that these two do not match. If there's no mapping between these types, could you suggest a Postgres type for a string arrays?

This is the code:

String list = "'{";
        for(int i=0; i<array.length; i++) {
            list+=prodotti[i]+",";
        }
        list+="}'";

        preparedStm.setString(4, list);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
giozh
  • 9,868
  • 30
  • 102
  • 183
  • Could you specify your case further by inserting a bit of code? – dezso May 24 '12 at 20:18
  • A `String` array (or `String[]`) does not match with `text[][]`, if it would work, it would be either `text[]` or you should use `String[][]` in Java. I am not saying that it will work, but at least match up your datatypes. – Mark Rotteveel May 24 '12 at 20:24
  • You should include sample data and the exact error message. Would make it easier to help you. – Erwin Brandstetter May 24 '12 at 21:26
  • the error just told me that the column is of type text[] but expression is of type character varying. I've edit my message with the part of code that throws exception – giozh May 25 '12 at 08:39
  • The **verbatim** error message should be the key part of your question. The part where `text` and `character varying` are mentioned could help to solve this quickly. – Erwin Brandstetter May 25 '12 at 11:08

1 Answers1

1

To understand multi-dimensional PostgreSQL array types consider the following quote from the manual:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

Internally, the types text[], text[][] are the same to PostgreSQL. If the column actually contains 2-dimensional text arrays, you'll have to match the dimensions in Java. But it could contain 1- or 3-dimensional arrays as well. PostgreSQL would allow it.

Also note that text and character varying (varchar) are different data types in PostgreSQL (while doing largely the same when varchar has no length modifier). Start by reading about character types in the manual.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228