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