Skip to content

Mssql: IDENTITY columns not detected in tables outside the default schema. #1586

Open
@nathanael-ruf

Description

@nathanael-ruf

I noticed this while migrating a mssql databse with many custom schemas to Postgres. The resulting columns in Postgres weren't SERIAL.

  • pgloader --version

    3.6.9 (this docker image because the db is on Azure), but I think I already know the cause which is still an issue in latest master.

    did you test a fresh compile from the source tree?

    No (first try failed because I'm on ARM), but as mentioned above can reproduce by running the query that is in master.

    did you search for other similar issues?

  • how can I reproduce the bug?

    CREATE SCHEMA pg_loader_test;
    CREATE TABLE pg_loader_test.MyTable (
        id INT IDENTITY(1,1) PRIMARY KEY
    );
    select c.TABLE_SCHEMA,
       c.TABLE_NAME,
       c.COLUMN_NAME,
       c.DATA_TYPE,
       c.IS_NULLABLE,
       COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity
    from INFORMATION_SCHEMA.COLUMNS c
        join INFORMATION_SCHEMA.TABLES t
            on c.TABLE_SCHEMA = t.TABLE_SCHEMA
            and c.TABLE_NAME = t.TABLE_NAME
    where c.TABLE_SCHEMA = 'pg_loader_test'
    • Set up a mssql instance (e.g. using docker: docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Secret123" -p 1433:1433 --name repro-mssql -d mcr.microsoft.com/mssql/server:2019-latest)
    • Run these queries:
    • Run the (simplified) "read" query from
      COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'),

=> IsIdentity is NULL, but expected is 1.

Replacing the selection with COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity seems to work as expected.

Activity

nathanael-ruf

nathanael-ruf commented on Jun 3, 2024

@nathanael-ruf
Author

I forked https://github.com/jahangiranwari/pgloader and fixed the bug. All my columns are now serial as expected.

added 2 commits that reference this issue on Jul 6, 2024
d51a9d6
74457f4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

      Mssql: IDENTITY columns not detected in tables outside the default schema. · Issue #1586 · dimitri/pgloader