Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Unanswered: PostgreSQL Insert Error

    OK then. A little background. I'ma PostgreSQL novice, though I've worked quite a bit with both Oracle 7 and SQL Server 2K. And, have done a LOT of front-end work using VB and to a lesser extent, ASP

    I saw that the 1st Win32 production release of PostrgreSQL is available for download, so I do so, and install it on my XP Pro. The install works like a charm, & I've now got PostgreSQL running as a service. Huzzah.

    I fire up pgAdmin III & log on. All looks OK; I create a a couple of groups and a guest user,add the default user to one of them. Create table tblTest (under Public schema) , with 4 columns. ID_PK (Serial), TimeStamp (Date, default = Now()), LastName (VarChar(25),Indexed) and FirstName (Varchar(20).

    All still looks good. I run pgAdmin's query tool, and issue the following SQL:
    Code:
    insert into public.tblTest (LastName, FirstName) Values ('Doh', 'John')
    and I get this error:

    ERROR: relation "public.tbltest" does not exist

    It's not in the manuals. Does this ring any bells for anyone? (I also tried it without the public. schemaname)
    Last edited by loquin; 02-18-05 at 03:17.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  2. #2
    Join Date
    Jun 2004
    Posts
    57

    define the schema first

    I believe you can must create a schema x. After that you must use the following syntax:

    set search_path to x

    After that use the insert phrase without the schema specified

    insert into tblTest (LastName, FirstName) Values ('Doh', 'John')

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    OK. I found a reference that helped over at the PostgreSQL site.

    http://archives.postgresql.org/pgsql...1/msg00012.php

    In essence, IF your table or field names contain upper case characters, you need to reference them surrounded by quotes.

    Code:
    Insert into "tblTest" ("Field1", "Field2") Values ('Value1', 'Value2')
    That's pretty eccentric, IMO. I understand that you may have case sensitivity in your table names or field names. That's normal. But, to require quote marks around a perfect match? That's odd.

    The key to this behavior is apparently these two facts:
    1. PostgreSQL object names are Case Specific.
    2. Non-Quoted object names default to lower case.
    Last edited by loquin; 02-21-05 at 13:27.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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