SQLFluff创建表多约束对齐不符合预期，求解决方案

问题描述 #

使用SQLFluff格式化PostgreSQL迁移脚本与查询语句时，遇到字段约束对齐不符合预期的情况：

• 当字段存在多约束（如not null unique与not null共存）时，SQLFluff以最后一个约束（如unique）为基准对齐，而非期望的第一个约束（not）
• 字段添加primary key generated always as identity约束后，SQLFluff以generated为基准对齐所有内容，完全忽略多约束对齐规则

理想格式化结果 #

create table tbl (
    foo   text not null,
    fooey int  not null,
    bar   text not null unique
);

实际格式化结果（多约束场景） #

create table tbl (
    foo   text          not null,
    fooey int           not null,
    bar   text not null unique
);

实际格式化结果（含identity约束场景） #

create table tbl (
    id    text primary key generated always as identity,
    foo   text             not null,
    fooey int              not null,
    bar   text not null unique
);

当前.sqlfluff配置 #

[sqlfluff]
templater = jinja
dialect = postgres
max_line_length = 0
exclude_rules = None
processes = 1

[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
indented_using_on = true
indented_on_contents = true
allow_implicit_indents = true
indented_joins = true
indented_ctes = true

[sqlfluff:layout:type:column]
spacing_before = align
align_within = statement
align_scope = file

[sqlfluff:layout:type:column_definition]
spacing_before = align
align_within = statement
align_scope = file

[sqlfluff:layout:type:column_constraint_segment]
spacing_before = align
align_within = statement
align_scope = file

[sqlfluff:layout:type:data_type]
spacing_before = align
align_within = statement
align_scope = file

问题分析与解决 #

这是SQLFluff布局规则的预期行为，核心原因是对column_constraint_segment设置了全局对齐，导致不同约束段的关键词被强制跨字段对齐，干扰了字段类型后的约束跟随逻辑。

调整后的配置 #

移除[sqlfluff:layout:type:column_constraint_segment]的对齐配置，仅保留字段类型（data_type）的对齐规则，确保约束直接跟随在类型后，不再强制对齐约束的某个关键词：

[sqlfluff]
templater = jinja
dialect = postgres
max_line_length = 0
exclude_rules = None
processes = 1

[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
indented_using_on = true
indented_on_contents = true
allow_implicit_indents = true
indented_joins = true
indented_ctes = true

[sqlfluff:layout:type:column]
spacing_before = align
align_within = statement
align_scope = file

[sqlfluff:layout:type:column_definition]
spacing_before = align
align_within = statement
align_scope = file

[sqlfluff:layout:type:data_type]
spacing_before = align
align_within = statement
align_scope = file

调整后效果 #

修改配置后，SQLFluff会先对齐所有字段的类型，约束直接跟在类型后，得到期望的格式化结果：

create table tbl (
    foo   text not null,
    fooey int  not null,
    bar   text not null unique
);

含identity约束的字段也会保持正确对齐：

create table tbl (
    id    text primary key generated always as identity,
    foo   text not null,
    fooey int  not null,
    bar   text not null unique
);

内容的提问来源于stack exchange，提问作者William M