alter() on PostgreSQL cannot change an attribute: MODIFY is a syntax error, and a comment change is ignored

未关闭
#1,625 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 6 天内回复

@dimitri-yatsenko 已经在做这个了。

开始于 2026年10月18日。

  • #1639 来自 @dimitri-yatsenko —— 未关闭

评估

难度
4/5
预计耗时
3-5 天
新手友好度
62/100
Issue 类型
缺陷
描述清晰度
描述清楚
活跃度
活跃
技术栈
postgresql, python, sql
领域
backend, database

调研方向

Inspect _make_attribute_alter in declare.py and run the supplied reproduction against PostgreSQL and MySQL to compare the generated SQL. Follow the existing type, nullability, default, rename, and comment comparison paths; PostgreSQL handling must emit backend-specific statements while preserving the core-type marker. Done means all listed PostgreSQL alterations succeed, comment-only changes are applied, and incompatible casts produce a clear error.

由索引模型根据 Issue 内容生成。

描述

bug

Summary

On PostgreSQL, Table.alter() can add, drop and rename attributes, but it cannot change one:

  • Changing an attribute's type, nullability or default raises a syntax error. _make_attribute_alter (declare.py) always emits MySQL's MODIFY <column definition>. PostgreSQL needs ALTER COLUMN ... TYPE ..., SET/DROP NOT NULL and SET/DROP DEFAULT.
  • Changing only an attribute's comment does nothing, silently. On PostgreSQL the comment is not part of the column definition: it is applied with COMMENT ON COLUMN. So a comment-only change compares as unchanged, and alter() reports nothing to alter.

MySQL handles all five changes.

Reproduction

DataJoint master, PostgreSQL 15 and MySQL 8.0:

import datajoint as dj

schema = dj.Schema("scratch_alter_pg")


@schema
class Measurement(dj.Manual):
    definition = """
    measurement_id : int32
    ---
    value : float32   # the value
    """


Measurement.definition = """
measurement_id : int32
---
value : float64   # the value
"""
Measurement.alter(prompt=False)
New definition of value MySQL PostgreSQL
value : float64 # the value altered QuerySyntaxError: syntax error at or near "MODIFY" (MODIFY "value" double precision NOT NULL)
value = null : float32 # the value altered same error (MODIFY "value" real DEFAULT NULL)
value = 1.5 : float32 # the value altered same error (MODIFY "value" real NOT NULL DEFAULT '1.5')
reading : float32 # the value {value} (rename) altered altered
value : float32 # another comment altered no change, no message

Expected behavior

On PostgreSQL, alter() makes the same changes it makes on MySQL:

  • a type change becomes ALTER COLUMN ... TYPE ..., with USING where the cast needs it, or a clear error when the backend cannot convert;
  • a nullability change becomes SET/DROP NOT NULL;
  • a default change becomes SET/DROP DEFAULT;
  • a comment change becomes COMMENT ON COLUMN, with the core-type marker kept, so describe() still round-trips.

Repositioning a column has no PostgreSQL equivalent; it is already skipped there (supports_column_position).

主要语言
Python
星标
198
派生
103
平均合并
6 天 1 小时
30 天内合并 PR
10

环境准备

  • 提供 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 阅读贡献指南

从这里开始

  1. 先读完整个 Issue,再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库,在一个分支上完成修改。
  4. 提交 Pull Request,并在描述里引用这个 Issue 编号。

datajoint/datajoint-python 的其他 Issue

查看 datajoint/datajoint-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。