How do I use the functions to get data from sequence objects?
For example, I need to know what is the value of a serial field after
inserting a new record; the one assigned to the record just inserted.

I have tried the psql interfase with the following:
select currval('restaurant.ordenes_clave_seq');
but I get a message telling me that "currval of sequence "ordenes_clave_seq"
is not yet defined in this session.

Please advice.

Respectfully,
Jorge Maldonado

Search Discussions

  • Brian Modra at Nov 3, 2009 at 6:15 am

    2009/11/2 JORGE MALDONADO <jorgemal1960@gmail.com>:
    How do I use the functions to get data from sequence objects?
    For example, I need to know what is the value of a serial field after
    inserting a new record; the one assigned to the record just inserted.

    I have tried the psql interfase with the following:
    select currval('restaurant.ordenes_clave_seq');
    but I get a message telling me that "currval of sequence "ordenes_clave_seq"
    is not yet defined in this session.
    Thats a "per session" function request...

    however, if you describe the sequence, you'll see something like this:

    \d dt_messages_sequence
    Sequence "public.dt_messages_sequence"
    Column | Type
    ---------------+---------
    sequence_name | name
    last_value | bigint
    increment_by | bigint
    max_value | bigint
    min_value | bigint
    cache_value | bigint
    log_cnt | bigint
    is_cycled | boolean
    is_called | boolean


    and then you can get the current value:

    trackerData=> select last_value from dt_messages_sequence;
    last_value
    ------------
    1208
    (1 row)

    ... however, if the sequence is being used in the session, then you
    must use currval rather than the select I described above... because
    it is thread-safe. This mechanism I described above may give you
    something less than the current value if the sequence is in use.
    Please advice.

    Respectfully,
    Jorge Maldonado


    --
    Brian Modra Land line: +27 23 5411 462
    Mobile: +27 79 69 77 082
    5 Jan Louw Str, Prince Albert, 6930
    Postal: P.O. Box 2, Prince Albert 6930
    South Africa
    http://www.zwartberg.com/
  • Andreas Kretschmer at Nov 3, 2009 at 6:22 am

    In response to JORGE MALDONADO :
    How do I use the functions to get data from sequence objects?
    For example, I need to know what is the value of a serial field after inserting
    a new record; the one assigned to the record just inserted.

    I have tried the psql interfase with the following:
    select currval('restaurant.ordenes_clave_seq');
    but I get a message telling me that "currval of sequence "ordenes_clave_seq" is
    not yet defined in this session.
    You have to use nextval() first, without nextval() the current value of
    the sequence are undefined in this session.

    In other words: first insert your new row into the table, after that
    call currval().


    Regards, Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
    GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
  • Lars Gustafsson at Nov 3, 2009 at 6:21 pm
    A client of mine hade a HD crash, he had a backup of his pgsql/data
    catalog, but when trying to start the server (on a newly installed
    postgres, same version he had) I get the following message:

    postgres$ FATAL: database files are incompatible with server
    DETAIL: The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT: It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?

    Regards,
    Lars.
  • Lonni J Friedman at Nov 3, 2009 at 6:24 pm
    How did you get the data onto the new server?
    On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson wrote:
    A client of mine hade a HD crash, he had a backup of his pgsql/data catalog,
    but when trying to start the server (on a newly installed postgres, same
    version he had) I get the following message:

    postgres$ FATAL:  database files are incompatible with server
    DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT:  It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?
    --
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    L. Friedman netllama@gmail.com
    LlamaLand https://netllama.linux-sxs.org
  • Lars Gustafsson at Nov 3, 2009 at 6:30 pm
    He had the /data directory on a USB memory

    Lars.


    3 nov 2009 kl. 19.24 skrev Lonni J Friedman:
    How did you get the data onto the new server?
    On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson wrote:
    A client of mine hade a HD crash, he had a backup of his pgsql/data
    catalog,
    but when trying to start the server (on a newly installed postgres,
    same
    version he had) I get the following message:

    postgres$ FATAL: database files are incompatible with server
    DETAIL: The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT: It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?
    --
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    L. Friedman netllama@gmail.com
    LlamaLand https://netllama.linux-sxs.org
  • Lonni J Friedman at Nov 3, 2009 at 6:33 pm
    That's never a recommended way to move data. You need to take the
    dump and import it into the new instance.
    On Tue, Nov 3, 2009 at 10:30 AM, Lars Gustafsson wrote:
    He had the /data directory on a USB memory

    Lars.


    3 nov 2009 kl. 19.24 skrev Lonni J Friedman:
    How did you get the data onto the new server?
    On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson wrote:

    A client of mine hade a HD crash, he had a backup of his pgsql/data
    catalog,
    but when trying to start the server (on a newly installed postgres, same
    version he had) I get the following message:

    postgres$ FATAL:  database files are incompatible with server
    DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT:  It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?


    --
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    L. Friedman netllama@gmail.com
    LlamaLand https://netllama.linux-sxs.org
  • Michael Wood at Nov 3, 2009 at 6:38 pm

    2009/11/3 Lars Gustafsson <gumse@mac.com>:
    A client of mine hade a HD crash, he had a backup of his pgsql/data catalog,
    but when trying to start the server (on a newly installed postgres, same
    version he had) I get the following message:

    postgres$ FATAL:  database files are incompatible with server
    DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT:  It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?
    This might not be it, but make sure you're using a 32 bit version of
    Postgres if the old server was 32 bits, and a 64 bit version if the
    old server was 64 bit.

    --
    Michael Wood <esiotrot@gmail.com>
  • Greg Stark at Nov 4, 2009 at 12:03 am

    On Tue, Nov 3, 2009 at 6:38 PM, Michael Wood wrote:
    2009/11/3 Lars Gustafsson <gumse@mac.com>:
    A client of mine hade a HD crash, he had a backup of his pgsql/data catalog,
    but when trying to start the server (on a newly installed postgres, same
    version he had) I get the following message:

    postgres$ FATAL:  database files are incompatible with server
    DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION
    906166272, but the server was compiled with PG_CONTROL_VERSION 822.
    HINT:  It looks like you need to initdb.


    Is it beyond repair ? Any last resorts ?
    This might not be it, but make sure you're using a 32 bit version of
    Postgres if the old server was 32 bits, and a 64 bit version if the
    old server was 64 bit.
    Close. In this case he's switching from a little-endian machine to
    big-endian or vice versa. 906166272 is 0x36030000 and 822 is
    0x00000336. Notice that the bytes are reversed. I think later versions
    check for this and print a more specific error message.

    The database is probably fine but the on-disk files are
    architecture-specific. You must put it on a machine with a very
    similar architecture.

    --
    greg

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 2, '09 at 7:16p
activeNov 4, '09 at 12:03a
posts9
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase