javapostgresqlcomposite-types

Unable to insert composite type via Java client to postgresql database: "value too long for type character(1)"


None of the existing answers seem to help the specific problem, which is - i have a table with a column of a composite type:

CREATE TYPE my_type AS (
    my_id INT4
    , my_other_id INT4
    , some_varchar VARCHAR(32)
    , is_true BOOL
    , some_char CHAR(1)
)

which is used in the table:

CREATE TABLE the_table
(
    id UUID
    , url VARCHAR(32)
    , name VARCHAR(32)
    , my_types _my_type NULL
)

And through java:

final String SQL = """
INSERT INTO the_table(
    id
    , url
    , name
    , my_types
)
VALUES (
    ?
    , ?
    , ?
    , ?
)
"""

// some stuff - like get connection
var statement = c.prepareStatement(SQL);
statement.setObject(1, model.getId());
statement.setObject(2, model.getUrl());
statement.setObject(3, model.getName());
statement.setObject(4, c.createArrayOf("my_type", model.getMyTypes());

// where the implementation of MyType::toString() is:
@Override
public String toString() {
    return String.format("(%d, %d, '%s', %b, '%c'),
        this.getId(), 
        this.getOtherId(),
        this.getName(),
        this.getIsTrue(),
        this.getSomeChar());

PROBLEM: I cannot avoid getting the error: "Value too long for type character(1)" - even though getSomeChar() is definitely a single character. The only way I can get it to run without erroring is to do change the toString() to:

return String.format("(%d, %d, '%s', %b, \"\")

which of course doesn't do what i want it to do (the char is null), but it does prove it is at least possible to persist this composite type in this manner. i don't know what i am doing wrong, or why it despises my attempt to push a single character.


Solution

  • The answer depends on which encoding you are using for your characters (and which characters getSomeChar can return). From this doc , some encodings, like utf-8, expect to have upto char(4) allocated to store a single character. Changing to CHAR(4) should cover all possible encodings:

    CREATE TYPE my_type AS (
    my_id INT4
    , my_other_id INT4
    , some_varchar VARCHAR(32)
    , is_true BOOL
    , some_char CHAR(4)
    

    )