2

I have interesting situation with Postgres (similar to PostgreSQL "Column does not exist" but it actually does). I.e. column or table name contains capital letters. In given link above it is about table name.

In my case, in database i have a column "projectId" defined in camelCase (SQL in DB looks like '.. "projectId" integer NOT NULL,..'). In Backend I use Java with Hibernate. When i try to get all entries of the table with named query e.g. "SELECT e FROM ProjectTa e" i get the error: 

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: column projectta0_.projectid does not exist
  Hinweis: Perhaps you meant to reference the column "projectta0_.projectId".
  Position: 138

SQL log: 

select projectta0_.id as id1_1_, projectta0_.budget as budget2_1_, projectta0_.completion as completi3_1_, projectta0_.name as name4_1_, projectta0_.projectId as projectI5_1_, p
rojectta0_.status as status6_1_, projectta0_.users as users7_1_ from project_ta projectta0_

'FROM "Tablename" ' will solve the problem for table name. How must i adjust my named query if I want just all entries of table. (i do not want to change column name or use name for @Column in my entity)

m19v
  • 1,800
  • 4
  • 11
  • 26
  • 1
    The error message suggests that you have a column with a name that looks like the table names and column name combined, all in quotes. – Thom Brown May 27 '20 at 23:45
  • I have e.g. tablename=project_ta, column=id and column="projectId". I do not think the second column is the combination of tablename and 1st column. If so, what can one do? – m19v May 27 '20 at 23:52
  • What do you get with `SELECT relname, attname FROM pg_class c INNER JOIN pg_attribute a ON c.oid = a.attrelid WHERE c.relname = 'projectta0_';` – Thom Brown May 28 '20 at 00:09
  • `postgres=# SELECT relname, attname FROM pg_class c INNER JOIN pg_attribute a ON c.oid = a.attrelid WHERE c.relname = 'project_ta0_'; relname | attname ---------+--------- (0 rows)` – m19v May 28 '20 at 00:15
  • ` relname | attname ------------+------------------------------ project_ta | ........pg.dropped.5........ project_ta | ........pg.dropped.6........ project_ta | budget project_ta | cmax project_ta | cmin project_ta | completion project_ta | ctid project_ta | id project_ta | name project_ta | projectId project_ta | status project_ta | tableoid project_ta | users project_ta | xmax project_ta | xmin ` – m19v May 28 '20 at 00:15
  • Okay, now try `SELECT relname, attname FROM pg_class c INNER JOIN pg_attribute a ON c.oid = a.attrelid WHERE a.attname like '%projectta0_%';` – Thom Brown May 28 '20 at 00:19
  • `SELECT relname, attname FROM pg_class c INNER JOIN pg_attribute a ON c.oid = a.attrelid WHERE a.attname like '%project%'; relname | attname ------------+------------ project_ta | project_id` – m19v May 28 '20 at 00:31
  • 1
    Another good example why you should avoid quoted identifiers. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names –  May 28 '20 at 05:44
  • @a_horse_with_no_name, thanks, helpful link. I have realised that also as stated in description of the question but also interested to adjust my code when i defined a column in db in camelCase. – m19v May 28 '20 at 07:51

0 Answers0