Clarify use and differences of composite and nested types about duckdb-web HOT 4 CLOSED

These are good points and they should be added to the docs. In general I think some of the confusion comes from using the different terminology for the same concepts, which should probably be cleaned up.

Here are some answers to your questions:

What is the difference (if any) between ARRAY, INT[], and LIST?

These are all different names for the same terminology. INT[] is an integer array, and LIST is what we call this internally.

Can the exact size of an array be used in a DDL statement (INT[3])? Are these limits respected?

Yes and no. The limits are ignored right now. You could simulate the enforcement through a CHECK constraint, though:

D create table integers(i int[3] check(len(i)<=3));
D insert into integers values ([1, 2, 3, 4, 5]);
Error: Constraint Error: Constraint Error: CHECK constraint failed: integers
D insert into integers values ([1, 2]);

How is the type of a LIST or ARRAY specified in a DDL statement?

LIST and ARRAY cannot be used in DDL statements. ARRAY can only be used for values (e.g. select array[1,2,3];), same for the list_value function (e.g. select list_value(1, 2, 3)). In DDL statements you need to use the type, e.g. INT[].

Can LIST expressions ([1, 2, 3]) be used to insert into ARRAYs?

Yes, they are the same. See above.

What is the difference (if any) between STRUCT, ROW, and MAP?

STRUCT and ROW are the same internally, but are different in that ROW constructors do not have explicit names, e.g.:

SELECT row(1, 'hello');
SELECT {'i': 1, 'j': 'hello'};

They can mostly be used interchangeably. Row elements will be given names v1, v2, etc.

MAP is different because every row can have different keys, e.g.:

select map(['key1', 'key2'], [1, 2]) union all select map(['key1'], [1]);

┌───────────────────────────────────────────────┐
│ map(list_value(key1, key2), list_value(1, 2)) │
├───────────────────────────────────────────────┤
│ {key1=1, key2=2}                              │
│ {key1=1}                                      │
└───────────────────────────────────────────────┘

Can STRUCT expressions ({'foo': ...}) be used to insert into MAPs or ROWs?

Rows are the same as structs, so yes. For maps this is not possible (yet) but should probably be possible, at least for maps with varchar keys.

from duckdb-web.

Thanks for the extensive answer! I figured most of this out by playing around in the CLI, but now this ^^ can be added to the docs.

from duckdb-web.

I think we can close this now!

from duckdb-web.

Yep!

from duckdb-web.