FAQ

[PostgreSQL] newid() in postgres

Marcel.beutner
Apr 4, 2007 at 7:14 am
Hello NG,

Is there an equivalent in postgres for the newid() function like in
sqlserver? I need to generate a unique identifier in my select
statement:

SELECT "X", newid(), "Y" FROM "MyTable"

X
newid()
Y
------------------------------------------------------------------------------------------------------------
1 139A7882-CF95-7C44-AC64-DF4D18614CAD test
2 D4CD37FE-4BD6-954B-B188-0D5BE0BDCF0E test2
...


I've searched in the groups already, but couldn't find any helpful
information - only to use a sequence, which returns just a number and
not a unique identifier.

Thanks in advance

Marcel
reply

Search Discussions

6 responses

  • Peter Eisentraut at Apr 4, 2007 at 10:56 pm

    marcel.beutner wrote:
    I've searched in the groups already, but couldn't find any helpful
    information - only to use a sequence, which returns just a number and
    not a unique identifier.
    Which properties do your unique identifiers posses that are not
    satisfied by a number returned by a sequence?
  • Postgres User at Apr 5, 2007 at 3:24 am
    Marcel,

    A sequence represents a unique identifier. You can call the function
    'nextval' to get the next unique value in the sequence. See related
    functions here:

    http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html

    In this code, I get the next sequence, insert it into a table, and
    then return the value to the calling function:

    DECLARE
    nextseq integer;

    BEGIN
    nextseq := nextval('entry_id_seq');

    INSERT INTO my_table (
    entry_id,
    entry_text,
    ) VALUES (
    nextseq,
    p_entry_text, -- input param
    );
    On 4/4/07, Peter Eisentraut wrote:
    marcel.beutner wrote:
    I've searched in the groups already, but couldn't find any helpful
    information - only to use a sequence, which returns just a number and
    not a unique identifier.
    Which properties do your unique identifiers posses that are not
    satisfied by a number returned by a sequence?

    --
    Peter Eisentraut
    http://developer.postgresql.org/~petere/

    ---------------------------(end of broadcast)---------------------------
    TIP 3: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq
  • Marcel.beutner at Apr 5, 2007 at 8:27 am
    Hello,

    Thanks a lot for your answers! But I don't need a sequence which only
    will be incremented. I need a _real_ GUID just as the newid()
    function. Is there no way to generate such a GUID?

    I need a real GUID because I use them further in my host app. And my
    host app relies on it.


    Thanks for your answers.

    Marcel
  • Postgres User at Apr 7, 2007 at 7:25 pm
    You'll need to create a custom function in Postgres to support this,
    which is fairly easy. It's been done before- do a search on Google:

    http://www.hclausen.net/psql.php
    On 5 Apr 2007 01:27:15 -0700, marcel.beutner wrote:
    Hello,

    Thanks a lot for your answers! But I don't need a sequence which only
    will be incremented. I need a _real_ GUID just as the newid()
    function. Is there no way to generate such a GUID?

    I need a real GUID because I use them further in my host app. And my
    host app relies on it.


    Thanks for your answers.

    Marcel


    ---------------------------(end of broadcast)---------------------------
    TIP 6: explain analyze is your friend
  • Marcel.beutner at Apr 8, 2007 at 4:52 pm
    Thanks,
    I'll try to implement it.

    Marcel
  • Chris Fischer at Apr 12, 2007 at 4:35 am
    Here's a PL/pgsql implementation I wrote.....I'm sure critics will be
    able to improve upon it:

    CREATE or REPLACE FUNCTION "common"."newid"()
    RETURNS "pg_catalog"."varchar" AS
    $BODY$
    DECLARE
    v_seed_value varchar(32);
    BEGIN
    select
    md5(
    inet_client_addr()::varchar ||
    timeofday() ||
    inet_server_addr()::varchar ||
    to_hex(inet_client_port())
    )
    into v_seed_value;

    return (substr(v_seed_value,1,8) || '-' ||
    substr(v_seed_value,9,4) || '-' ||
    substr(v_seed_value,13,4) || '-' ||
    substr(v_seed_value,17,4) || '-' ||
    substr(v_seed_value,21,12));
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

Related Discussions

Discussion Navigation
viewthread | post