MySQL转PostgreSQL查询适配：补全COLUMN_TYPE与SEQ_IN_INDEX字段

需求背景 #

现有MySQL和PostgreSQL两个数据库副本，需要将原MySQL的列信息查询转换为PostgreSQL版本，当前尝试的查询缺失COLUMN_TYPE和SEQ_IN_INDEX字段，同时需要用Java程序实现该功能。

原MySQL查询 #

SELECT cols.COLUMN_NAME, cols.ORDINAL_POSITION, cols.DATA_TYPE, cols.COLUMN_TYPE, 
       sta.SEQ_IN_INDEX,
       COALESCE(cols.NUMERIC_PRECISION, cols.DATETIME_PRECISION) as DATA_WIDTH
FROM information_schema.`COLUMNS` as cols
LEFT JOIN information_schema.statistics as sta
    ON sta.TABLE_SCHEMA = cols.TABLE_SCHEMA
    AND sta.TABLE_NAME = cols.TABLE_NAME
    AND sta.COLUMN_NAME = cols.COLUMN_NAME
    AND sta.INDEX_NAME = 'primary'
WHERE cols.TABLE_SCHEMA = 'schema1'
    AND cols.TABLE_NAME  = 'TAB_TABLE1'
ORDER BY cols.ORDINAL_POSITION 

完整PostgreSQL查询 #

PostgreSQL没有直接对应MySQL的COLUMN_TYPE和SEQ_IN_INDEX字段，需要通过关联系统表和字段拼接来实现：

SELECT 
    cols.column_name,
    cols.ordinal_position,
    cols.data_type,
    -- 模拟MySQL COLUMN_TYPE，拼接带参数的类型字符串
    CASE
        WHEN cols.data_type IN ('character varying', 'varchar') THEN concat('varchar(', cols.character_maximum_length, ')')
        WHEN cols.data_type = 'character' THEN concat('char(', cols.character_maximum_length, ')')
        WHEN cols.data_type IN ('numeric', 'decimal') THEN concat('numeric(', cols.numeric_precision, ', ', cols.numeric_scale, ')')
        WHEN cols.data_type IN ('timestamp without time zone', 'timestamp with time zone') THEN cols.data_type
        ELSE cols.data_type
    END AS column_type,
    -- 主键列的顺序，对应MySQL SEQ_IN_INDEX
    kcu.ordinal_position AS seq_in_index,
    COALESCE(cols.numeric_precision, cols.datetime_precision) AS data_width
FROM information_schema.columns cols
LEFT JOIN information_schema.key_column_usage kcu
    ON cols.table_catalog = kcu.table_catalog
    AND cols.table_schema = kcu.table_schema
    AND cols.table_name = kcu.table_name
    AND cols.column_name = kcu.column_name
    AND kcu.constraint_name = 'primary' -- 筛选主键约束
WHERE cols.table_schema = 'schema1'
    AND cols.table_name = 'TAB_TABLE1' -- 注意表名大小写，根据实际情况调整是否用LOWER()
ORDER BY cols.ordinal_position;

字段说明

• COLUMN_TYPE：通过CASE语句根据数据类型拼接参数，覆盖常见类型（可根据业务扩展其他类型），和MySQL的COLUMN_TYPE格式保持一致。
• SEQ_IN_INDEX：关联information_schema.key_column_usage表筛选主键约束，用该表的ordinal_position表示主键列的顺序，非主键列返回NULL，和原MySQL查询的LEFT JOIN逻辑一致。

Java实现示例 #

使用JDBC执行上述查询，处理结果集用于补全表缺失列：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PostgresColumnInfoFetcher {
    private static final String DB_URL = "jdbc:postgresql://localhost:5432/your_db_name";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";
    private static final String COLUMN_INFO_QUERY = """
            SELECT 
                cols.column_name,
                cols.ordinal_position,
                cols.data_type,
                CASE
                    WHEN cols.data_type IN ('character varying', 'varchar') THEN concat('varchar(', cols.character_maximum_length, ')')
                    WHEN cols.data_type = 'character' THEN concat('char(', cols.character_maximum_length, ')')
                    WHEN cols.data_type IN ('numeric', 'decimal') THEN concat('numeric(', cols.numeric_precision, ', ', cols.numeric_scale, ')')
                    WHEN cols.data_type IN ('timestamp without time zone', 'timestamp with time zone') THEN cols.data_type
                    ELSE cols.data_type
                END AS column_type,
                kcu.ordinal_position AS seq_in_index,
                COALESCE(cols.numeric_precision, cols.datetime_precision) AS data_width
            FROM information_schema.columns cols
            LEFT JOIN information_schema.key_column_usage kcu
                ON cols.table_catalog = kcu.table_catalog
                AND cols.table_schema = kcu.table_schema
                AND cols.table_name = kcu.table_name
                AND cols.column_name = kcu.column_name
                AND kcu.constraint_name = 'primary'
            WHERE cols.table_schema = ?
                AND cols.table_name = ?
            ORDER BY cols.ordinal_position;
            """;

    public static void main(String[] args) {
        // 资源自动关闭（try-with-resources）
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(COLUMN_INFO_QUERY)) {

            // 设置查询参数：schema名和表名
            pstmt.setString(1, "schema1");
            pstmt.setString(2, "TAB_TABLE1");

            try (ResultSet rs = pstmt.executeQuery()) {
                // 遍历结果集，处理列信息
                while (rs.next()) {
                    String columnName = rs.getString("column_name");
                    int ordinalPosition = rs.getInt("ordinal_position");
                    String dataType = rs.getString("data_type");
                    String columnType = rs.getString("column_type");
                    // 处理非主键列的NULL值
                    Integer seqInIndex = rs.getInt("seq_in_index");
                    if (rs.wasNull()) seqInIndex = null;
                    Integer dataWidth = rs.getInt("data_width");
                    if (rs.wasNull()) dataWidth = null;

                    // 这里添加补全表缺失列的业务逻辑
                    System.out.printf("列名：%s | 位置：%d | 数据类型：%s | 带参类型：%s | 主键顺序：%s | 数据宽度：%s%n",
                            columnName, ordinalPosition, dataType, columnType, seqInIndex, dataWidth);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

注意事项

1. JDBC驱动依赖：确保项目中引入PostgreSQL JDBC驱动，Maven依赖示例：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version> <!-- 使用最新稳定版本 -->
</dependency>

2. NULL值处理：非主键列的seq_in_index会返回NULL，需通过rs.wasNull()判断后赋值，避免得到默认0值。
3. 表名大小写：PostgreSQL默认将未加引号的表名转为小写，若你的表名是大写（如TAB_TABLE1），需确保查询时的表名与实际一致，或根据情况调整为LOWER('TAB_TABLE1')。

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