Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unhappy Unanswered: SELECT MAX(pk) FROM .... (constraint????)

    Hi,

    I have a litte problem with my private key. I use the following statement:

    SELECT MAX(person_pk)+1 FROM person;

    in a VALUES statement, in a sql String, in a Java Program.

    The problem is, when there is not one entry in the table then something works wrong. Is there a way to add maybe a constraint to my expresion saying (the change has to be done in the SQL string):
    if person_pk is Null (is it null when there is no entry?) take 0
    else take MAX(person_pk)+1

    Thx for any help
    Last edited by pir2004; 04-05-04 at 03:48.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    coalesce(max(person_pk),0)+1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    9

    coalesce(max(person_pk),0)+1 ??

    Thank you,

    I found info saying, that coalesce takes the first value that is not null, so in the case of nothing beeing in the table it takes 0 but doesnt it then add 1 to it?

    shouldn´t be it like(i don´t know, it is just a question):
    coalesce(max(person_pk)+1,0) ??

    and can I then write

    INSERT INTO person ( person_pk,..)
    VALUES( ( coalesce(max( person_pk),0 )+1 FROM person ) ,...)
    Last edited by pir2004; 04-05-04 at 08:15.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you need to do some research on sequences

    http://developer.postgresql.org/docs...esequence.html

    do not try to insert max+1 back into the same table

    you will get integrity problems in a multi-user environment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    9

    sorry to bug again

    1.) I am sorry, but I am new to SQL. Why did you send me the link to sequences? I don´t really get the point of them. Is it to be used instead of the max +1?

    2.) The program I am writing is actually not intended to be used in a multiuser environmen, but you have a point there.
    But even sequenz is not multiuser friendly, is it?

    3.)is there a way to take the next free key? (because if I delete an entry, with my system I am wasting space, because I will never fill the free slots again)


    4.)in the worst case could i use the following without any changes?:
    INSERT INTO person ( person_pk,..)
    VALUES( ( coalesce(max( person_pk),0 )+1 FROM person ) ,...)
    Last edited by pir2004; 04-05-04 at 08:45.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1.) Why did you send me the link to sequences?

    sequences are used to generate numbers, typically for use as primary keys, and they are safe to use in a multi-user environment


    2.) Where could I read something about getting the max value of a primary key and then taking the next higher key?

    no idea, because it's not a very safe technique


    3.) ... if I delete an entry, with my system I am wasting space,
    because I will never fill the free slots again

    actually that's not true, no space is wasted

    leave the gaps, they do not hurt in any way


    4.)in the worst case could i use the following without any changes?:

    could you? you be the judge, just try it

    you can learn a lot about sql by testing queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Posts
    9

    sequnce

    Ok, I read a little about sequences about the manipulation functions, but still can't find out, how to incorporate a sequence in a already exiting table. I wrote the following file:

    DROP TABLE person;
    DROP SEQUENCE incpkperson;

    CREATE TABLE person(person_pk INT8,firstname VARCHAR(100),lastname VARCHAR(100),picture BYTEA,CONSTRAINT pkperson PRIMARY KEY (person_pk));
    CREATE SEQUENCE incpkperson START 0 INCREMENT 1;

    but how do i tell the db that incpkperson and person_pk is the same value?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up the NEXTVAL function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Posts
    9

    nextval

    I am sorry, but I didn't post everything I think I know.

    I guess I could use:

    INSERT INTO person (person_pk,firstname, lastname,picture)
    VALUE(nextval('incpkperson'), me, me,...);

    What I meant is that I don`t see how it connects between person_pk and incpkperson, because these are two different things.
    I already tried
    1.) insert a value with nextval -> inserts an entry with 1
    2.) insert a value writing it into the db by hand, setting person_pk to 2 manualy -> inserts an entry with 2
    3.) insert a value with nextval ->
    ERROR: Cannot insert a duplicate key into unique index pkperson

    I guess this is not so bad, as I do not insert any person_pk manually, but what if I wanted to. That was actually my question. Sorry for that.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sequences are separate from tables, and that is to their advantage

    it's like a popup stack of new numbers

    if you ask for a new number, you will get it

    in your example, it is step 2 that is wrong -- don't do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2004
    Posts
    9

    Thanks a lot

    Thank you very much for your help. I did learn a lot with your help. Great that you take your time to help others. Thanks once again.

Posting Permissions

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