Help get this topic noticed by sharing it on Twitter, Facebook, or email.

PostgreSQL for iPhone's background SQL statements need quotation marks

You need to change the queries to automatically surround table names with quotation marks. All my table names are uppercase but because your program does not surround the names quotation marks, I cannot see data in my tables without manually creating a SQL statement and inserting my own quotation marks. The same goes for column names. So just put in quotation marks in your background SQL statements.
1 person has
this problem
+1
Reply
  • Walter (Customer Support Representative) September 30, 2010 03:23
    We do put quotes around schema elements (tables, columns, etc) that have illegal characters in them (spaces, etc).

    I just tested with an uppercase table ("create table public.TEST_TABLE_UPPERCASE (ID int primary key, Name varchar);"), and I am able to see it show up in the schema. The name is converted to lowercase in the database, but tapping on the table name does show the rows for me.

    Can you let me know what the name of the table is? And are you sure even the lower-case version of the table is not showing up?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Not trying to be rude, but is there any reason you wouldn't surround all table names? Otherwise, the server side is definitely going to fold the table names to lowercase. If the table names are already lowercase, the quotation marks are unnecessary but won't hurt anything.

    Here's an example:

    I have a table named PRODUCT and a table named STUDENT in a schema named jfreeman (all lowercase).

    Using the iPhone app, I can successfully see both the STUDENT and PRODUCT tables. Once I tap on either of them, though, I get a message like this: the relation "jfreeman.student" does not exist. Notice how the PostgreSQL error is reporting that the table name is lowercase; that's because PostgreSQL is folding it to lowercase automatically on the server side.

    However, if I manually run this SQL statement:

    SELECT * FROM jfreeman."STUDENT";

    I can see all the tuples in the STUDENT table. That's why I'm led to believe that you simply need to surround *all* table names in the iPhone app, because the server is definitely going to fold them to lowercase without the quotation marks.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Walter (Customer Support Representative) September 30, 2010 20:36
    Good point. We'll fix that for the next release to always quote schema components for PostgreSQL.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated