Can't insert records to a table with two level nested composite types arrays

[eitansela]

I'm submitting a bug

• [ x] bug report
• feature request

So we have a composite type 'place':

CREATE TYPE place AS ( name TEXT, altitude DECIMAL(10,2), latitude DECIMAL(10,2) )

And we have a composite type 'city' in which they can be many 'places' - 'places' is an array of composite type place:

CREATE TYPE city AS ( city TEXT, zip_code BIGINT, places place[] )

And the main table 'employee' in which there is employeeCities, which is an array of composite type 'city', because he is responsible for few cities:
CREATE TABLE employee ( employeeName varchar(100) NOT NULL, employeeId varchar(11) NOT NULL , employeeAddress varchar(100) DEFAULT NULL, employeeEmail varchar(100) DEFAULT NULL, employeeCities city[] DEFAULT NULL, PRIMARY KEY (employeeId) );

There are two ways to insert data to this table.

1st option - using ARRAY and ROW:
INSERT INTO employee (employeename, employeeid, employeeaddress, employeeemail, employeecities) VALUES('name_1', 'id_1', 'address_1', 'email_1', ARRAY[ROW('city_1',111,ARRAY[ROW('place_1',22.11,11.22)::place,ROW('place_2',33.44,44.33)::place])::city,ROW('city_2',222,null)::city]);

2nd option - using backspaces and quotes:
INSERT INTO employee (employeename, employeeid, employeeaddress, employeeemail, employeecities) VALUES('name_1', 'id_1', 'address_1', 'email_1', '{"(Jerusalem,111,\"{\"\"(place_1,1.11,11.1)\"\",\"\"(place_2,2.11,11.2)\"\"}\")","(TLV,222,{})","(Haifa,333,{})"}');

So where the problem is?
We are trying to insert data to this table using Java, Spring Boot application, and we use PostgreSQL JDBC Driver version 42.2.5.

To format the nested composite types array, we use:

conn = template.getJdbcTemplate().getDataSource().getConnection();
Array citiesArray = conn.createArrayOf("city", citiesItems.toArray());

This is the recommended way to do it.
However, we get the following exception:

org.postgresql.util.PSQLException: ERROR: malformed array literal: "{(place_1,1.11,11.1)"

If we have a table with only one nested array - aka employee->employeeCities (without places) it will work!
The problem occurs only when you have 2 level nested composite types arrays:

employee
|-employeeCities
|-places

Did you encounter a scenario of this kind before?
Do you have knowledge of how to solve that problem?

Driver Version - 42.2.5

Java Version - 1.8.0_192

OS Version - macos mojave

PostgreSQL Version - 10.6

To Reproduce
Steps to reproduce the behaviour:

CREATE TYPE place AS ( name TEXT, altitude DECIMAL(10,2), latitude DECIMAL(10,2) )

CREATE TYPE city AS ( city TEXT, zip_code BIGINT, places place[] )

CREATE TABLE employee ( employeeName varchar(100) NOT NULL, employeeId varchar(11) NOT NULL , employeeAddress varchar(100) DEFAULT NULL, employeeEmail varchar(100) DEFAULT NULL, employeeCities city[] DEFAULT NULL, PRIMARY KEY (employeeId) );

INSERT INTO employee (employeename, employeeid, employeeaddress, employeeemail, employeecities) VALUES('name_1', 'id_1', 'address_1', 'email_1', ARRAY[ROW('city_1',111,ARRAY[ROW('place_1',22.11,11.22)::place,ROW('place_2',33.44,44.33)::place])::city,ROW('city_2',222,null)::city]);
This works.

INSERT INTO employee (employeename, employeeid, employeeaddress, employeeemail, employeecities) VALUES('name_1', 'id_1', 'address_1', 'email_1', '{"(Jerusalem,111,\"{\"\"(place_1,1.11,11.1)\"\",\"\"(place_2,2.11,11.2)\"\"}\")","(TLV,222,{})","(Haifa,333,{})"}');
This also works.

`package com.sample.postgress.entity;

import java.util.List;

public class City {

String city;
Long zip_code;
List<Place> places;

public City(String city, Long zip_code, List<Place> places) {
    this.city = city;
    this.zip_code = zip_code;
    this.places = places;
}

public Long getZip_code() {
    return zip_code;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public List<Place> getPlaces() {
    return places;
}

@Override
public String toString() {
    return "("+city+","+zip_code+","+places+")";
}

}
`

`package com.sample.postgress.entity;

import java.sql.Array;
import java.util.List;

public class CityItem {

String city;
Long zip_code;
Array places;

public CityItem(String city, Long zip_code, Array places) {
    this.city = city;
    this.zip_code = zip_code;
    this.places = places;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public Array getPlaces() {
    return places;
}

@Override
public String toString() {
    return "("+city+","+zip_code+","+places+")";
}

}
`

`package com.sample.postgress.entity;

public class Place {

String name;
Double altitude;
Double latitude;

public Place(String name, Double altitude, Double latitude) {
    this.name = name;
    this.altitude = altitude;
    this.latitude = latitude;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public Double getAltitude() {
    return altitude;
}

public void setAltitude(Double altitude) {
    this.altitude = altitude;
}

public Double getLatitude() {
    return latitude;
}

public void setLatitude(Double latitude) {
    this.latitude = latitude;
}

@Override
public String toString() {
    return "("+name+","+altitude+","+latitude+")";
}

}
package com.sample.postgress.entity;

public class Employee {

String employeeId;
String employeeName;
String employeeEmail;
String employeeAddress;


public String getEmployeeEmail() {
	return employeeEmail;
}
public void setEmployeeEmail(String employeeEmail) {
	this.employeeEmail = employeeEmail;
}
public String getEmployeeId() {
	return employeeId;
}
public void setEmployeeId(String employeeId) {
	this.employeeId = employeeId;
}
public String getEmployeeName() {
	return employeeName;
}
public void setEmployeeName(String employeeName) {
	this.employeeName = employeeName;
}
public String getEmployeeAddress() {
	return employeeAddress;
}
public void setEmployeeAddress(String employeeAddress) {
	this.employeeAddress = employeeAddress;
}

}
`

`package com.sample.postgress.dao;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sample.postgress.entity.City;
import com.sample.postgress.entity.CityItem;
import com.sample.postgress.entity.Place;
import org.postgresql.jdbc.PgArray;
import org.postgresql.util.GT;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.sample.postgress.entity.Employee;
import com.sample.postgress.mapper.EmployeeRowMapper;
@repository
public class EmployeeDaoImpl implements EmployeeDao{

public EmployeeDaoImpl(NamedParameterJdbcTemplate template) {  
    this.template = template;  

}
NamedParameterJdbcTemplate template;

@Override
public List<Employee> findAll() {
	return template.query("select * from employee", new EmployeeRowMapper());
}

@Override
public void insertEmployee(Employee emp) {
	 final String sql = "insert into employee(employeeId, employeeName , employeeAddress,employeeEmail, employeeCities) values(:employeeId,:employeeName,:employeeEmail,:employeeAddress, :employeeCities)";


	Connection conn;
	try {

		ArrayList<City> cities = new ArrayList<City>();
		List<Place> places1 = new ArrayList<Place>();
		places1.add(new Place("place_1",1.11,11.1));
		places1.add(new Place("place_2",2.11,11.2));
		places1.add(new Place("place_3",3.11,11.3));
		cities.add(new City("Jerusalem",111L, places1));

		List<Place> places2 = new ArrayList<Place>();
		places2.add(new Place("place_4",1.22,22.1));
		places2.add(new Place("place_5",2.22,22.2));
		places2.add(new Place("place_6",3.22,22.3));

// cities.add(new City("TLV",222L, places2));

		List<Place> places3 = new ArrayList<Place>();
		places3.add(new Place("place_7",1.33,33.1));
		places2.add(new Place("place_8",2.33,33.2));
		places3.add(new Place("place_9",3.33,33.3));

// cities.add(new City("Haifa",333L, places3));

		conn = template.getJdbcTemplate().getDataSource().getConnection();

		ArrayList<CityItem> citiesItems = new ArrayList<CityItem>();
		for (City city : cities) {
			Array placesArray = conn.createArrayOf("place", city.getPlaces().toArray());
			CityItem cityItem = new CityItem(city.getCity(), city.getZip_code(),placesArray);
			citiesItems.add(cityItem);
		}

		Array citiesArray = conn.createArrayOf("city", citiesItems.toArray());
		System.out.println(citiesArray);



	KeyHolder holder = new GeneratedKeyHolder();
        SqlParameterSource param = new MapSqlParameterSource()
				.addValue("employeeId", emp.getEmployeeId())
				.addValue("employeeName", emp.getEmployeeName())
				.addValue("employeeEmail", emp.getEmployeeEmail())
				.addValue("employeeAddress", emp.getEmployeeAddress())
				.addValue("employeeCities", citiesArray);
        template.update(sql,param, holder);
	}
	catch (SQLException e) {
		e.printStackTrace();
	}
 
}

@Override
public void updateEmployee(Employee emp) {
	 final String sql = "update employee set employeeName=:employeeName, employeeAddress=:employeeAddress, employeeEmail=:employeeEmail where employeeId=:employeeId";
	 
        KeyHolder holder = new GeneratedKeyHolder();
        SqlParameterSource param = new MapSqlParameterSource()
				.addValue("employeeId", emp.getEmployeeId())
				.addValue("employeeName", emp.getEmployeeName())
				.addValue("employeeEmail", emp.getEmployeeEmail())
				.addValue("employeeAddress", emp.getEmployeeAddress());
        template.update(sql,param, holder);
 
}

@Override
public void executeUpdateEmployee(Employee emp) {
	 final String sql = "update employee set employeeName=:employeeName, employeeAddress=:employeeAddress, employeeEmail=:employeeEmail where employeeId=:employeeId";
		 

	 Map<String,Object> map=new HashMap<String,Object>();  
	 map.put("employeeId", emp.getEmployeeId());
	 map.put("employeeName", emp.getEmployeeName());
	 map.put("employeeEmail", emp.getEmployeeEmail());
	 map.put("employeeAddress", emp.getEmployeeAddress());

	 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
		    @Override  
		    public Object doInPreparedStatement(PreparedStatement ps)  
		            throws SQLException, DataAccessException {  
		        return ps.executeUpdate();  
		    }  
		});  

 
}

@Override
public void deleteEmployee(Employee emp) {
	 final String sql = "delete from employee where employeeId=:employeeId";
		 

	 Map<String,Object> map=new HashMap<String,Object>();  
	 map.put("employeeId", emp.getEmployeeId());

	 template.execute(sql,map,new PreparedStatementCallback<Object>() {  
		    @Override  
		    public Object doInPreparedStatement(PreparedStatement ps)  
		            throws SQLException, DataAccessException {  
		        return ps.executeUpdate();  
		    }  
		});  

 
}

}
`
The insertEmployee method will throw exception.

Expected behaviour
A record should have been inserted to employee table. However, we get an exception.

Logs
postgress-boot-master-1.log

pgjdbc-trace.log

[Miha-x64]

Not sure we've faced the same problem, but my reproducer is rather trivial:

create table tmp (
    "value" int[][]
)

insert into tmp values (array[array[]]) -- error
insert into tmp values (array[array[]::int[]]) -- ok
insert into tmp values ('{{}}') -- error — and this happens with JDBC to me
insert into tmp values ('{{}::int[]}') -- does not help
insert into tmp values ('{array[]::int[]}') -- neither

I was unable to find how to declare empty arrays with '{}' syntax. Thus, the driver should use array[] syntax, and array[]::type for empty arrays.