Add support for altering struct columns (adding fields, dropping fields, renaming fields) #17003

Mytherin · 2025-04-05T08:16:03Z

This PR adds support for altering struct columns to the SQL dialect. The syntax follows the regular ALTER syntax for columns - but just identifies struct fields through qualifiers.

Syntax:

CREATE TABLE test(s STRUCT(i INTEGER, j INTEGER));
INSERT INTO test VALUES (ROW(1, 1)), (ROW(2, 2));

Adding a field

-- add field "k INTEGER" to struct "s" in table "test"
ALTER TABLE test ADD COLUMN s.k INTEGER;
FROM test;
┌─────────────────────────────────────────┐
│                    s                    │
│ struct(i integer, j integer, k integer) │
├─────────────────────────────────────────┤
│ {'i': 1, 'j': 1, 'k': NULL}             │
│ {'i': 2, 'j': 2, 'k': NULL}             │
└─────────────────────────────────────────┘

Dropping a field

-- drop field "i" from struct "s" in table "test"
ALTER TABLE test DROP COLUMN s.i;
FROM test;
┌──────────────────────────────┐
│              s               │
│ struct(j integer, k integer) │
├──────────────────────────────┤
│ {'j': 1, 'k': NULL}          │
│ {'j': 2, 'k': NULL}          │
└──────────────────────────────┘

Renaming a field

-- renaming field "j" of struct "s" to "v1" in table test"
ALTER TABLE test RENAME s.j TO v1;
FROM test;
┌───────────────────────────────┐
│               s               │
│ struct(v1 integer, k integer) │
├───────────────────────────────┤
│ {'v1': 1, 'k': NULL}          │
│ {'v1': 2, 'k': NULL}          │
└───────────────────────────────┘

Implementation

The actual alter commands are currently implemented through a rewrite to a ChangeColumnType using a struct_remap expression. While this works - it has the drawback that existing (unchanged) fields are also rewritten. In the future, we can work on optimizing this to work similarly to adding/dropping/renaming columns which do not touch the other columns of a table.

…ST` and `MAP` columns. (#17462) This PR extends #17003 Using `element` to target the child of a `LIST`, or `key`/`value` to target the key/value of a `MAP` respectively. ### Examples #### `MAP` ```sql s MAP( STRUCT(n INTEGER, m INTEGER), STRUCT(i INTEGER, j INTEGER) ) ``` `ALTER TABLE test ADD COLUMN s.key.k INTEGER` Turns the schema into: ```sql s MAP( STRUCT(n INTEGER, m INTEGER, k INTEGER), STRUCT(i INTEGER, j INTEGER) ) ``` #### `LIST` ```sql s STRUCT(i INTEGER, j INTEGER)[] ``` `ALTER TABLE test ADD COLUMN s.element.k INTEGER` Turns the schema into: ```sql s STRUCT(i INTEGER, j INTEGER, k INTEGER)[] ```

Add support for altering struct columns (adding fields, dropping fields, renaming fields) (duckdb/duckdb#17003) Clean up format script, gather all files then run concurrently instead of running concurrently per directory (duckdb/duckdb#16988)

Mytherin added 5 commits April 4, 2025 14:50

Add support for ALTER TABLE ADD COLUMN a.b.c to add fields to a struct

c1545dd

Add support for dropping fields from structs

d6046db

Generate files

0ff5069

Use column_names[0] now

895196f

Add support for renaming fields of a struct

7155bff

Mytherin added the Needs Documentation Use for issues or PRs that require changes in the documentation label Apr 5, 2025

duckdblabs-bot mentioned this pull request Apr 5, 2025

[duckdb/#17003] - Add support for altering struct columns (adding fields, dropping fields, renaming fields) needs documentation duckdb/duckdb-web#5194

Closed

Mytherin merged commit ba0c2a7 into duckdb:main Apr 7, 2025
51 of 52 checks passed

8000

Tishj mentioned this pull request May 13, 2025

Add support for altering structs (drop, add, rename field) inside LIST and MAP columns. #17462

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add support for altering struct columns (adding fields, dropping fields, renaming fields) #17003

Add support for altering struct columns (adding fields, dropping fields, renaming fields) #17003

Uh oh!

Uh oh!

Uh oh!

Add support for altering struct columns (adding fields, dropping fields, renaming fields) #17003

Add support for altering struct columns (adding fields, dropping fields, renaming fields) #17003

Conversation

Adding a field

Dropping a field

Renaming a field

Implementation

Uh oh!

Uh oh!

Uh oh!