Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    14

    Unanswered: Sequence column does not exist

    Hi this is probably something stupid on my part by I am selecting from the following table definition:

    Code:
    CREATE TABLE users
    (
      firstname "varchar"(20),
      lastname "varchar"(20),
      emailaddress "varchar"(40),
      telephone "varchar"(20),
      lastvisited "timestamp",
      "userID" int4 NOT NULL DEFAULT nextval('public."users_userID_seq"'::text),
      CONSTRAINT "pk_userID" UNIQUE ("userID")
    )
    userID is an auto incremented attribute. Now when operating the following query on data I know to exist.
    Code:
    -- Executing query:
    SELECT userID, emailAddress, firstName, lastName, telephone, lastVisited FROM users WHERE userID = 1
    
    ERROR:  column "userid" does not exist
    If I do a select * from users then it displays userID with the value 1. Do you know why this is?

    Thank you.

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    select

    why do you write userID instead of userid?
    postgres is in most cases case sensitive be aware of that

    did you created the sequence "users_userID_seq" ?

    what version are you using?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Jul 2004
    Posts
    14
    Hi eperich,

    Thanks for taking a look.

    Using the beta version 8 on windows at the moment. Can put it across to my linux box which runs 7.4 I believe to cross-reference if necessary.

    The attribute names seem to be converted all to lower-case even though I defined them as above. Just tried using all lower case and that doesn't seem to help either. The sequence has been defined and does show up in the PGSQL admin tool.

  4. #4
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    15
    It's because you created the "userID" column with quotes. Postgres is case-*IN*sensitive by default (currently folding to lowercase), unless you quote names. So by creating "userID" as you did, you need to reference that column with quotes.
    ie.
    SELECT "userID", emailAddress, firstName, lastName, telephone, lastVisited FROM users WHERE userID = 1;

  5. #5
    Join Date
    Jul 2004
    Posts
    14
    Very cool Slush - it worked!

    Using interactive SQL on a new table definition:
    Code:
    create table user2(
      userID serial primary key,
      firstName varchar(30),
      lastName varchar(30));
    
    select * from user2 where userid = 1;
    Works great. Thanks.

Posting Permissions

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