G_Hosa_Phat - 2019-01-17

Example from logs (redacted):

java -jar "<PATH>\schemaSpy.jar" -t pgsql -host <SERVERNAME>:<PORT> -u <USERNAME> -p <PASSWORD> -db <DBNAME> -all -noviews -ahic -dp "<PATH>\postgresql-42.1.4.jar" -gv "C:\Program Files (x86)\Graphviz2.38" -hq -o "C:\SchemaSpy\"

23:22:45.116 WARNING: Table.initColumnAutoUpdate - Failed to determine auto increment status: org.postgresql.util.PSQLException: ERROR: relation "test.Notes" does not exist
  Position: 15
23:22:45.116 WARNING: Table.initColumnAutoUpdate - SQL: select * from Test."Notes" where 0 = 1

23:22:46.180 WARNING: Table.fetchNumRows - Unable to extract the number of rows for table Notes, using '-1'
23:22:46.180 WARNING: Table.fetchNumRows - org.postgresql.util.PSQLException: ERROR: relation "test.Notes" does not exist
  Position: 22

Note in the Table.initColumnAutoUpdate warning that includes the SQL statement sent to the database, the table name ("Notes") is properly quoted, while the schema name (Test) does not have quotation marks, even though it is mixed-case. The PSQLException bears this out in the ERROR lines where we see that "relation "test.Notes" does not exist. The schema name has been converted to lower-case by the PostgreSQL database because it was not quoted in the actual SQL statement.

Now, perhaps it's considered "bad form" to create mixed-case database object names. I don't know, and don't really care about opinions on database design theory and such at this point as this is the structure of the database I have to work with. Regardless, however, the fact that it is possible means that there will be those like me who actually have mixed-case object names like this. For example, the table identified in the sample above has a definition with a CREATE statement that looks like this:

CREATE TABLE "Test"."Notes"
(
  "ID" integer NOT NULL DEFAULT nextval('"Test"."ID_seq"'::regclass),
  "OwnerID" integer,
  "Note" text,
  "User" character varying(60),
  "DateTime" timestamp with time zone,
  CONSTRAINT "Note_PK" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Test"."Notes"
  OWNER TO <DEV/ADMIN USER GROUP>;
GRANT SELECT ON TABLE "Test"."Notes" TO <END USER GROUP>;
GRANT ALL ON TABLE "Test"."Notes" TO <DEV/ADMIN USER GROUP>;

Because PostgreSQL is actually (contrary to what you might find on the InterWebz) case-sensitive for all object names - database, schema, table, column, function, view, etc. - that were explicitly created in mixed case (created using quotation marks to define the name in mixed-case), all queries need some sort of parsing/validation for object names.

 

Last edit: G_Hosa_Phat 2019-01-17