SQL
Struct Data Type
Conceptually, a `STRUCT` column contains an ordered list of columns called “entries”. The entries are referenced by name using strings. This document refers to those entry names as keys. Each row in the `STRUCT` column must have the same keys. The names of the struct entries are part of the *schema*. Each row in a `STRUCT` column must have the same layout. The names of the struct entries are case-insensitive.  
`STRUCT`s are typically used to nest multiple columns into a single column, and the nested column can be of any type, including other `STRUCT`s and `LIST`s.  
`STRUCT`s are similar to PostgreSQL's `ROW` type. The key difference is that DuckDB `STRUCT`s require the same keys in each row of a `STRUCT` column. This allows DuckDB to provide significantly improved performance by fully utilizing its vectorized execution engine, and also enforces type consistency for improved correctness. DuckDB includes a `row` function as a special way to produce a `STRUCT`, but does not have a `ROW` data type. See an example below and the [`STRUCT` functions documentation](#docs:sql:functions:struct) for details.  
`STRUCT`s have a fixed schema. It is not possible to change the schema of a `STRUCT` using `UPDATE` operations.  
See the [data types overview](#docs:sql:data_types:overview) for a comparison between nested data types.  
#### Creating Structs {#docs:sql:data_types:struct::creating-structs}  
Structs can be created using the [`struct_pack(name := expr, ...)`](#docs:sql:functions:struct) function, the equivalent array notation `{'name': expr, ...}`, using a row variable, or using the `row` function.  
Create a struct using the `struct_pack` function. Note the lack of single quotes around the keys and the use of the `:=` operator:  
```sql
SELECT struct_pack(key1 := 'value1', key2 := 42) AS s;
```  
Create a struct using the array notation:  
```sql
SELECT {'key1': 'value1', 'key2': 42} AS s;
```  
Create a struct using a row variable:  
```sql
SELECT d AS s FROM (SELECT 'value1' AS key1, 42 AS key2) d;
```  
Create a struct of integers:  
```sql
SELECT {'x': 1, 'y': 2, 'z': 3} AS s;
```  
Create a struct of strings with a `NULL` value:  
```sql
SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'} AS s;
```  
Create a struct with a different type for each key:  
```sql
SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345} AS s;
```  
Create a struct of structs with `NULL` values:  
```sql
SELECT {
'birds': {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'},
'aliens': NULL,
'amphibians': {'yes': 'frog', 'maybe': 'salamander', 'huh': 'dragon', 'no': 'toad'}
} AS s;
```  
#### Adding Field(s)/Value(s) to Structs {#docs:sql:data_types:struct::adding-fieldsvalues-to-structs}  
Add to a struct of integers:  
```sql
SELECT struct_insert({'a': 1, 'b': 2, 'c': 3}, d := 4) AS s;
```  
#### Retrieving from Structs {#docs:sql:data_types:struct::retrieving-from-structs}  
Retrieving a value from a struct can be accomplished using dot notation, bracket notation, or through [struct functions](#docs:sql:functions:struct) like `struct_extract`.  
Use dot notation to retrieve the value at a key's location. In the following query, the subquery generates a struct column `a`, which we then query with `a.x`.  
```sql
SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);
```  
If a key contains a space, simply wrap it in double quotes (` "`).  
```sql
SELECT a."x space" FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a);
```  
Bracket notation may also be used. Note that this uses single quotes (` '`) since the goal is to specify a certain string key and only constant expressions may be used inside the brackets (no expressions):  
```sql
SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a);
```  
The struct_extract function is also equivalent. This returns 1:  
```sql
SELECT struct_extract({'x space': 1, 'y': 2, 'z': 3}, 'x space');
```