Skip to content

Saving a null Postgres enum results in a varchar value #1935

Open
@mattmcc-attest

Description

@mattmcc-attest

Hi, I have an issue with enums using spring-data-jdbc.

I have a Java enum type:

public enum Sentiment {
  POSITIVE,
  NEGATIVE,
  NEUTRAL
}

And a corresponding enum type on one of my tables:

insights=> \dT+ sentiment
                                          List of data types
 Schema |   Name    | Internal name | Size | Elements |    Owner    | Access privileges | Description 
--------+-----------+---------------+------+----------+-------------+-------------------+-------------
 public | sentiment | sentiment     | 4    | POSITIVE+| owner |                   | 
        |           |               |      | NEGATIVE+|             |                   | 
        |           |               |      | NEUTRAL  |             |                   | 
(1 row)

I have this Entity (see the Sentiment field):

@Table("responses")
public record Response(
    @Id UUID id,
    UUID roundId,
    UUID cardTemplateId,
    UUID cardId,
    UUID answerOptionTemplateId,
    UUID answerOptionId,
    String text,
    Sentiment sentiment) {}

If I call save on the repository:

responseRepository.save(new RoundResponse(Id, roundId, UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), "text", null)); // sentiment is null

I get the error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "sentiment" is of type sentiment but expression is of type character varying

I have some Converters for handling the enums but because the value is null it doesn't reach these converters. It seems to me that the spring-data-jdbc library must be at some point trying to convert the null value to a varchar? (maybe by default?)

This is not what I would expect, I would expect if the value I am inserting is null and my enum type postgres field is nullable then null should be used rather than varchar :)

Let me know if you need any more information, thank you!

Activity

self-assigned this
on Nov 11, 2024
schauder

schauder commented on Nov 11, 2024

@schauder
Contributor

I think it isn't Spring Data JDBC that tries to convert something here. Instead it seems to tell Postgres, this null value is a VARCHAR` and Postgres is overwhelmed by the task to convert it to an enum ...

mattmcc-attest

mattmcc-attest commented on Nov 11, 2024

@mattmcc-attest
Author

Yeah I debugged into the JdbcTemplate and could see it was trying to insert with sqlType = 12 = varchar. Not sure at what point spring-data-jdbc is telling Postgres is is a varchar though

mattmcc-attest

mattmcc-attest commented on Nov 11, 2024

@mattmcc-attest
Author

@schauder Do you think this is a bug? It seems like the MappingJdbcConverter doesn't recognise the type so converts it to a String.

Should there be some function further up the call stack that handles nulls whereby if the value is null then don't try and type it and insert as a null in the DB?

schauder

schauder commented on Nov 12, 2024

@schauder
Contributor

Yes, I do think this is a bug. Could you provide a full reproducer, preferable as github repo?

mattmcc-attest

mattmcc-attest commented on Nov 15, 2024

@mattmcc-attest
Author

@schauder Thank you :) here is an app that reproduces it https://github.com/mattmcc-attest/animal

mattmcc-attest

mattmcc-attest commented on Nov 26, 2024

@mattmcc-attest
Author

^ is the above useful for you? @schauder

schauder

schauder commented on Nov 27, 2024

@schauder
Contributor

Yes, I think this will do. Just need to find time to look into it.

mattmcc-attest

mattmcc-attest commented on Nov 27, 2024

@mattmcc-attest
Author

no problem, thanks for your help

serezakorotaev

serezakorotaev commented on Jun 3, 2025

@serezakorotaev
Contributor

Hi, everyone!
@schauder Are you sure that this is a bug? I wrote some tests in this repo like this

	@Test
	void enumParameterIsNotNullReturnCorrectSqlTypeFromConverter() {

		WithEnumEntity entity = new WithEnumEntity(23L, DummyEnum.ONE);

		SqlParametersFactory sqlParametersFactory = createSqlParametersFactoryWithConverters(
				singletonList(WritingEnumConverter.INSTANCE));

		SqlIdentifierParameterSource sqlParameterSource = sqlParametersFactory.forInsert(entity,
				WithEnumEntity.class, Identifier.empty(), IdValueSource.PROVIDED);

		assertThat(sqlParameterSource.getValue("id")).isEqualTo(23L);
		assertThat(sqlParameterSource.getValue("dummy_enum")).isEqualTo(DummyEnum.ONE.name());
		assertThat(sqlParameterSource.getSqlType("dummy_enum")).isEqualTo(1111);
	}

	@Test
	void enumParameterIsNullReturnCorrectSqlTypeFromConverter() {
		WithEnumEntity entity = new WithEnumEntity(23L, null);

		SqlParametersFactory sqlParametersFactory = createSqlParametersFactoryWithConverters(
				singletonList(WritingEnumConverter.INSTANCE));

		SqlIdentifierParameterSource sqlParameterSource = sqlParametersFactory.forInsert(entity,
				WithEnumEntity.class, Identifier.empty(), IdValueSource.PROVIDED);

		assertThat(sqlParameterSource.getValue("id")).isEqualTo(23L);
		assertThat(sqlParameterSource.getSqlType("dummy_enum")).isEqualTo(1111);
		assertThat(sqlParameterSource.getValue("dummy_enum")).isNull();
	}

	@WritingConverter
	enum WritingEnumConverter implements Converter<DummyEnum, JdbcValue> {

		INSTANCE;

		@Override
		public JdbcValue convert(DummyEnum source) {
			return JdbcValue.of(source.name().toUpperCase(), JDBCType.OTHER);
		}
	}

The first test is correct because inside converters we have type information by which can find custom converter WritingEnumConverter and convert it.

The second test is incorrect because we don't know what type information for value we have. But even if we do this, for example like this (addConvertedNullableProperty is my custom method)

	if (value == null) {
		TypeInformation<?> typeInformation = property.getTypeInformation();
		addConvertedNullableProperty(parameters, property, typeInformation, paramName);
	} else {
		addConvertedPropertyValue(parameters, property, value, paramName);
	}

And added some new logic where we can find a specific converter, as a result of this convert null object -> null JdbcValue.

Also in the method's documentation by Converter#convert method source must be not null. Hence, MappingJdbcConverter will choose default converter by target type (is a String)

serezakorotaev

serezakorotaev commented on Jun 3, 2025

@serezakorotaev
Contributor

As a new approach, I can suggest that you set the default JDBC Type.NULL for null values. I tested this approach on https://github.com/mattmcc-attest/animal the repositories and animal were saved correctly.

I can push changes and a few tests so that you can see this approach :)

7 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

    Development

    Participants

    @schauder@spring-projects-issues@serezakorotaev@mattmcc-attest

    Issue actions

      Saving a `null` Postgres enum results in a varchar value · Issue #1935 · spring-projects/spring-data-relational