Mapping PostgreSQL text[][] type and Java type

Question

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);

Could you specify your case further by inserting a bit of code? — András Váczi
– András Váczi, Commented May 24, 2012 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
– Mark Rotteveel, Commented May 24, 2012 at 20:24
You should include sample data and the exact error message. Would make it easier to help you. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 24, 2012 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
– giozh, Commented May 25, 2012 at 8: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
– Erwin Brandstetter, Commented May 25, 2012 at 11:08

Erwin Brandstetter · Accepted Answer · 2022-01-25 22:30:15Z

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.

edited Jan 25, 2022 at 22:30

answered May 24, 2012 at 21:25

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Mapping PostgreSQL text[][] type and Java type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related