Menu ▾ ▴

#1527 Postgresql: Some Objects->Table tabs have problems with mixed-case table names

SQuirreL
open
nobody
None
medium
2024-05-17
2024-04-06
John Hardin
No

When viewing Postgres table metadata in the Objects UI, some of the tabs do not properly retrieve the table data if the table name contains any uppercase characters.

Repro:

  1. Create a table with a mixed-case name (e.g. VersionInfo) and insert some rows
  2. Create another table in the same schema with an all-lowercase name and insert some rows
  3. Refresh object tree and metadata cache
  4. In the Objects tab open {conn}->{schema}->Table->VersionInfo
  5. Info tab displays correctly
  6. Content tab is empty even though there are rows
  7. Row Count tab is empty even though there are rows (it doesn't even display zero)
  8. Columns tab displays correctly, as do the remaining tabs (apparently)
  9. Click on the other table in the schema, having the all-lowercase name
  10. Info tab displays correctly
  11. Content tab displays correctly
  12. Row Count tab displays correctly
  13. Click back on the mixed-case-name table
  14. Info tab displays correctly
  15. Content tab is empty even though there are rows
  16. Row Count tab is empty even though there are rows (it doesn't even display zero)

It looks like there's a problem with quoting table names in the queries underlying those two tabs when the table name is not all-lowercase, as SELECT * FROM {schema}.VersionInfo; works properly in the SQL tab.

Environment:
Windows 11 64-bit
Squirrel 4.7.1 standard (build number is not available in About dialog :( - downloaded 2024-04-04)
Adoptium OpenJDK 21.0.2+13-LTS 64bit
Postgres 16.2 hosted locally
Postgres JDBC 42.7.1

Discussion

  • John Hardin

    John Hardin - 2024-04-06

    Argh!

    SELECT * FROM {schema}."VersionInfo"; (quoted) works properly in the SQL tab.

    Copied that at the wrong point in my repro testing and SF doesn't let you edit your bug reports.

    :facepalm

     

    Last edit: John Hardin 2024-04-06

  • Gerd Wagner

    Gerd Wagner - 2024-05-16

    Sorry, I can't reproduce your problem. Heres my system:
    Suse Linux Leap 15.5
    Open JDK 11.0.10+9
    Postgres 16.3 hosted locally
    Postgres JDBC 42.7.3
    SQuirreL latest snapshot

    Here's the script I used:

    create schema sf_bug_no_1527

create table sf_bug_no_1527.VersionInfo
(
versId integer not NULL PRIMARY KEY,
versName Varchar(200)
)

INSERT INTO sf_bug_no_1527.VersionInfo(versId,versName) VALUES (1, 'Ver1');
INSERT INTO sf_bug_no_1527.VersionInfo(versId,versName) VALUES (2, 'Vers2');
INSERT INTO sf_bug_no_1527.VersionInfo(versId,versName) VALUES (3, 'Vers3');

SELECT * FROM sf_bug_no_1527.VersionInfo


create table sf_bug_no_1527.versioninfozwei
(
vers2Id integer not NULL PRIMARY KEY,
vers2Name Varchar(200)
)

INSERT INTO sf_bug_no_1527.versioninfozwei(vers2Id,vers2Name) VALUES (10, 'Vers10');
INSERT INTO sf_bug_no_1527.versioninfozwei(vers2Id,vers2Name) VALUES (20, 'Vers20');
INSERT INTO sf_bug_no_1527.versioninfozwei(vers2Id,vers2Name) VALUES (30, 'Vers30');

SELECT * FROM sf_bug_no_1527.versioninfozwei
     

    Last edit: Gerd Wagner 2024-05-16

    • John Hardin

      John Hardin - 2024-05-16

      On Thu, 16 May 2024, Gerd Wagner wrote:

      Sorry, I can't reproduce your problem. Heres my system:
      Suse Linux Leap 15.5
      Open JDK 11.0.10+9
      Postgres 16.3 hosted locally
      Postgres JDBC 42.7.3

      Here's the script I used:
      ~~~
      create schema sf_bug_no_1527

      create table sf_bug_no_1527.VersionInfo

      I think PG lowercases object names automatically unless they are enclosed
      in single quotes.

      Try:

        create table sf_bug_no_1527.'VersionInfo'

      --
      John Hardin KA7OHZ http://www.impsec.org/~jhardin/
      jhardin@impsec.org pgpk -a jhardin@impsec.org
      key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79

      1,447 days since the first private commercial manned orbital mission (SpaceX)

       

  • Gerd Wagner

    Gerd Wagner - 2024-05-17

    create table sf_bug_no_1527."VersionInfo"
    worked for me and I can now reproduce the problem.

    To solve the problem go to menu File --> Global Preferences --> tab SQL Scripts and check
    "Qualify table names in generated scripts with the schema name"
    and
    "Use double quotes (") for qualifying"

     

Log in to post a comment.

MongoDB Logo MongoDB