Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Question Unanswered: Case sensitivty for table names

    I have just migrated a MySQL db over to Postgres v8.4. I have experienced some metadata retrieval errors and I am wondering if it has anything to do with case sensitivity.

    The old query (in PHP to the MySQL db) reads like this: $sql = "SELECT * from fooBar WHERE fooBarID = $myFooBarId";

    I have a log set up to see the value of $myFooBarId but it comes back with no value, yet the information IS in the database.

    In phppgadmin I noticed that all the tables and entity names are all lower case.

    Does this require me to change all the table and entity names in my queries to lowercase as well, so that the query does not fail?

    I'm a newbie to Postgres so any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Chaund View Post
    I have just migrated a MySQL db over to Postgres v8.4. I have experienced some metadata retrieval errors and I am wondering if it has anything to do with case sensitivity.

    The old query (in PHP to the MySQL db) reads like this: $sql = "SELECT * from fooBar WHERE fooBarID = $myFooBarId";
    What datatype is the column fooBarId? (Post the CREATE TABLE statement)
    What datatype is the "myFooBarId" variable?
    What happens when you run that statement manually using e.g. psql?

    Does this require me to change all the table and entity names in my queries to lowercase as well, so that the query does not fail?
    The case of the table name has nothing to do with comparing values, or are you getting an error such as "table not found"?

  3. #3
    Join Date
    Jan 2010
    Posts
    4

    Question

    >What datatype is the column fooBarId? (Post the CREATE TABLE statement)

    CREATE TABLE fooBar (
    fooBarAttributeId SERIAL PRIMARY KEY,
    fooBarID integer NOT NULL,
    createdDate integer NOT NULL,
    lastUpdate integer NOT NULL,
    UNIQUE (fooBarId)
    );


    >What datatype is the "myFooBarId" variable?

    $myFooBarId = $this->db->qstr($this->x['fooBarId']);
    So myFooBarID would be an integer.


    >What happens when you run that statement manually using e.g. psql?

    Works from command line and I get proper value returned.

    New Info:
    My class contains an array:

    public $x = array("fooBarAttributeId" => 0, "fooBarId" => 0, "lastUpdate" => "now()", "createdDate" => "now()", "vemg" => array() );

    This array() is called multiple times within the class.

    Is the reason the database value can not be retrieved is because the field is in double quotes within the array ("fooBarId" => 0) ?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Chaund View Post
    My class contains an array:

    public $x = array("fooBarAttributeId" => 0, "fooBarId" => 0, "lastUpdate" => "now()", "createdDate" => "now()", "vemg" => array() );

    This array() is called multiple times within the class.

    Is the reason the database value can not be retrieved is because the field is in double quotes within the array ("fooBarId" => 0) ?
    I have no idea what the array in PHP is doing to your SQL statement nor what "the array is called" is supposed to mean.
    This is definitely not something that happens in SQL or Postgres

    Just post the real SQL statement that is being built by your application, e.g. by printing it out just before execution. (Which should always be the first step when debugging SQL problems that stem from a programming or scripting language)

    If the column is indeed quoted using double quotes in the resulting statement, then it is case-sensitive.

    But if this would be the case you would get some kind of error message instead of an empty result.
    You do catch any errors do you?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •