SQLPro Studio is showing all postgres fields as using using identity defaults
Closed this issue · 9 comments
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:
- Open SQLPro Studio
- Connect to postgres database
- 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
Hi. What version of Postgres are you connecting to? If you connect to the sample database do you see this as well?
I see this on a local Postgres database running 14.9. I also see this same behavior on Aurora Postgres 15.4.
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?
| 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.
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?
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,Awesome, thanks for letting me know.