SQLPro Studio is showing all postgres fields as using using identity defaults

[sinklair]

Describe the bug Using SQLPro Studio 2024.21 connected postgres, I'm seeing all fields showing with the default value of generated by default as identity

This is also coming through when you script a table to the clipboard or a window.

To Reproduce Steps to reproduce the behavior:

1. Open SQLPro Studio
2. Connect to postgres database
3. Expand any table to show the fields

Expected behavior Correct definition of each field

Screenshots

Environment details (please complete the following information):

• Device: MacBook Pro 14" 2021
• OS: macOS 14.4.1
• SQLPro app Version SQLPro Studio 2024.21
  • Installation source: Website
  • Target database server postgres

[hankinsoft]

Hi. What version of Postgres are you connecting to? If you connect to the sample database do you see this as well?

[sinklair]

I see this on a local Postgres database running 14.9. I also see this same behavior on Aurora Postgres 15.4.

[hankinsoft]

If you run this query:

    SELECT a.attname, a.attidentity, s.seqstart, s.seqincrement
    FROM pg_attribute a
    JOIN pg_class c ON a.attrelid = c.oid
    JOIN pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_sequence s ON c.oid = s.seqrelid
    WHERE c.relname = '{TABLE_NAME}'
    AND n.nspname = '{TABLE_SCHEMA}'
    AND a.attnum > 0; -- to ensure we're looking at table columns and not system/internal ones

And replace TABLE_NAME and TABLE_SCHEMA with your actual schema/names, what do you get for results?

[sinklair]

attname	attidentity	seqstart	seqincrement
id		NULL	NULL
state_abbrev		NULL	NULL
m_release		NULL	NULL
m_name		NULL	NULL
geometry		NULL	NULL
name		NULL	NULL

Not sure if it matters but attidentity is coming back as blank, not NULL.

[hankinsoft]

If you get the chance, could you give this build a try:

https://sqlprostudio.s3.us-east-1.amazonaws.com/studio/SQLProStudio.2024.30.app.zip

And let me know if that sorts it?

[sinklair]

That has solved it for display in the database tree, but it is still showing when I right click a table and Script As > Create to > New Query Window.

    statefp character varying(2) GENERATED BY DEFAULT AS IDENTITY,
    countyfp character varying(3) GENERATED BY DEFAULT AS IDENTITY,
    cousubfp character varying(5) GENERATED BY DEFAULT AS IDENTITY,
    cousubns character varying(8) GENERATED BY DEFAULT AS IDENTITY,
    geoid character varying(10) GENERATED BY DEFAULT AS IDENTITY,

[hankinsoft]

https://sqlprostudio.s3.us-east-1.amazonaws.com/studio/SQLProStudio.2024.31.app.zip?

[sinklair]

That did it.

On Wed, May 15, 2024 at 1:44 PM Kyle Hankinson @.***> wrote:

https://sqlprostudio.s3.us-east-1.amazonaws.com/studio/SQLProStudio.2024.31.app.zip ?

— Reply to this email directly, view it on GitHub https://github.com/hankinsoft/SQLPro/issues/976#issuecomment-2113108523, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAQR3VHYZHGX2OL2JWSKOEDZCONHDAVCNFSM6AAAAABHQ3UFXWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJTGEYDQNJSGM . You are receiving this because you authored the thread.Message ID: @.***>

[hankinsoft]

Awesome, thanks for letting me know.