sql: auto array type cast from '{}' in column default definition for better pg compatibility

[kimxogus]

Is your feature request related to a problem? Please describe.
I'm using scala backend and https://github.com/slick/slick and https://github.com/tminglei/slick-pg, and I'm trying to use cockroach with them.

slick-pg generates sql like below with array column defaults.

create table test {
  foo text[] default '{}'
}

In postgres, that SQL works.

But in cockroach, Error: pq: expected DEFAULT expression to have type string[], but ''{}'' has type string occurs and sql should be fixed as below.

create table test {
  foo text[] default '{}'::text[]
}

Describe the solution you'd like
Automatically cast '{}' to its own column type.

Describe alternatives you've considered
Modify https://github.com/tminglei/slick-pg to generate sql with type cast like ::text[], but I think fixing in cockroach would be better in pg-compatibility.

Additional context

[knz]

This overlaps with, but is not fully duplicate of, #23299

[khalibartan]

@jordanlewis Can you help me getting started with this one?

[jordanlewis]

This issue requires adding some functionality to the "constant up-cast" system in CockroachDB. This subsystem lives in pkg/sql/sem/tree/constant.go. Its job is to take constants, which can either be numeric or string-like, and resolve them into real types in the type system.

The trouble in this case is that we don't have a way to upcast a string into an array. The relevant code here is StrVal.ResolveAsType in the file I mentioned above. You'll need to add a type switch for array types in the second half of that function, and reuse the array parsing code from pkg/sql/sem/tree/parse_array.go (ParseDArrayFromString) to actually get an array type out from the string.

Does that help?