Thread: Sequences

<p><font color="#808080" face="Verdana" size="2">I have various sequences in my database set up for ids.  My question
is: How can I get the current value of the sequence without creating a session and using the currval
function?</font><p><fontcolor="#808080" face="Verdana" size="2">I would like to take the current value of the sequence
anduse it for a value in an insert statement.</font><br /><p><font color="#808080" face="Verdana"
size="2">Example:</font><p>       <font color="#808080" face="Verdana" size="2">insert into users ( "user_id",
"user_fname","user_lname", "user_email" )</font><br />        <font color="#808080" face="Verdana" size="2">values (
'currentsequence', 'Ray', 'Hunter', 'rhunter@enterasys.com' );</font><p><font color="#808080" face="Verdana"
size="2">Thanks,</font><p><b><fontface="Verdana" size="2">Ray Hunter</font></b><br /><font face="Verdana"
size="2">FirmwareEngineer</font><p><b><font face="Verdana" size="2">ENTERASYS NETWORKS</font></b><p><font
face="Verdana"size="2">Phone:          801 887-9888</font><br /><font face="Verdana" size="2">Fax:            801
972-5789</font><br/><font face="Verdana" size="2">Email:          </font><a
href="mailto:rhunter@enterasys.com"><u><fontcolor="#0000FF" face="Verdana"
size="2">rhunter@enterasys.com</font></u></a><br/><font face="Verdana" size="2">www:            </font><a
href="http://www.enterasys.com"><u><fontcolor="#0000FF" face="Verdana" size="2">www.enterasys.com</font></u></a> 

On Mon, Feb 11, 2002 at 08:43:23AM -0500, "Hunter, Ray" <rhunter@enterasys.com> wrote:
> I have various sequences in my database set up for ids.  My question is:
> How can I get the current value of the sequence without creating a session
> and using the currval function?
> 
> I would like to take the current value of the sequence and use it for a
> value in an insert statement.
> 
> 
> Example:
> 
>     insert into users ( "user_id", "user_fname", "user_lname",
> "user_email" )
>     values ( 'current sequence', 'Ray', 'Hunter',
> 'rhunter@enterasys.com' );

I think you want to use nextval in this context. currval is used when you
have already gotten a new sequence number and want to use it in several
inserts in the same transaction.

If you make user_id a serial type, then its default value will be nextval
and you could use:
insert into users ( "user_fname", "user_lname", "user_email" ) values ('Ray', 'Hunter', 'rhunter@enterasys.com' );
to add a row.

<p><font size="2">The only problem with this solution is that I have already incremented the sequence and need to use
thecurrent value?  Is there a way to assign the value to a variable and then use it in an insert
statement?</font><p><fontsize="2">This is just a hashed out example.  I am hoping it can all be done in sql
statements...</font><br/><font size="2">Example:</font><br />        <font size="2">var num = select last_value from
user_table_id_seq;</font><p>       <font size="2">insert into users ( "user_id", "user_fname", "user_lname",
"user_email")</font><br /><font size="2">        values ( 'num', 'Ray', 'Hunter', 'rhunter@enterasys.com' );</font><br
/><p><fontsize="2">If I could do this is would be great...Is it possible?</font><br /><p><font
size="2">Thanks,</font><p>       <p><font size="2">Ray Hunter</font><br /><font size="2">Firmware
Engineer</font><p><fontsize="2">ENTERASYS NETWORKS</font><br /><p><font size="2">-----Original Message-----</font><br
/><fontsize="2">From: Bruno Wolff III [<a href="mailto:bruno@wolff.to">mailto:bruno@wolff.to</a>] </font><br /><font
size="2">Sent:Monday, February 11, 2002 7:37 AM</font><br /><font size="2">To: Hunter, Ray</font><br /><font
size="2">Cc:pgsql-sql@postgresql.org</font><br /><font size="2">Subject: Re: [SQL] Sequences</font><br /><p><font
size="2">OnMon, Feb 11, 2002 at 08:43:23AM -0500,</font><br /><font size="2">  "Hunter, Ray"
<rhunter@enterasys.com>wrote:</font><br /><font size="2">> I have various sequences in my database set up for
ids. My question </font><br /><font size="2">> is: How can I get the current value of the sequence without creating
a</font><br /><font size="2">> session and using the currval function?</font><br /><font size="2">> </font><br
/><fontsize="2">> I would like to take the current value of the sequence and use it for </font><br /><font
size="2">>a value in an insert statement.</font><br /><font size="2">> </font><br /><font size="2">>
</font><br/><font size="2">> Example:</font><br /><font size="2">> </font><br /><font size="2">>       insert
intousers ( "user_id", "user_fname", "user_lname", </font><br /><font size="2">> "user_email" )</font><br /><font
size="2">>      values ( 'current sequence', 'Ray', 'Hunter', 'rhunter@enterasys.com' </font><br /><font
size="2">>);</font><p><font size="2">I think you want to use nextval in this context. currval is used when you have
alreadygotten a new sequence number and want to use it in several inserts in the same transaction.</font><p><font
size="2">Ifyou make user_id a serial type, then its default value will be nextval and you could use: insert into users
("user_fname", "user_lname", "user_email" )</font><p><font size="2">  values ('Ray', 'Hunter', 'rhunter@enterasys.com'
);</font><br/><font size="2">to add a row.</font> 

Further to Brunos' suggestion, <p>if you require to use the  same id for insertion in other tables (slaves) you should
<br/>assign the value to a variable and use that value for inserting <br />in other referencing tables eg <p> $id =
$dbh-> selectrow_array("select nextval('seq_name')");  (# perl specific) <p><font color="#000000"> <font
face="Verdana"><fontsize="-1">insert into users ( "user_id", "user_fname", "user_lname", "user_email"
)</font></font></font><br/><font color="#000000">        <font face="Verdana"><font size="-1">values ( $id , 'Ray',
'Hunter','rhunter@enterasys.com' );</font></font></font><font face="Verdana"><font color="#000000"><font
size="-1"></font></font></font><p><fontface="Verdana"><font color="#000000"><font size="-1"> insert into user_details (
"user_id", ..... other details ....  )</font></font></font><br /><font color="#000000">         <font
face="Verdana"><fontsize="-1">values ( $id ,  .... other details .... );</font></font></font><br /><font
face="Verdana"><fontcolor="#000000"><font size="-1"></font></font></font>  <br /><font color="#000000"></font>  <br /> 
<br/>  <br />  <p>"Hunter, Ray" wrote: <blockquote type="CITE">  <p><font face="Verdana"><font color="#808080"><font
size="-1">Ihave various sequences in my database set up for ids.  My question is:  How can I get the current value of
thesequence without creating a session and using the currval function?</font></font></font><p><font
face="Verdana"><fontcolor="#808080"><font size="-1">I would like to take the current value of the sequence and use it
fora value in an insert statement.</font></font></font><p><font face="Verdana"><font color="#808080"><font
size="-1">Example:</font></font></font><p>       <font face="Verdana"><font color="#808080"><font size="-1">insert into
users( "user_id", "user_fname", "user_lname", "user_email" )</font></font></font><br />        <font
face="Verdana"><fontcolor="#808080"><font size="-1">values ( 'current sequence', 'Ray', 'Hunter',
'rhunter@enterasys.com');</font></font></font><p><font face="Verdana"><font color="#808080"><font
size="-1">Thanks,</font></font></font><p><b><fontface="Verdana"><font size="-1">Ray Hunter</font></font></b><br /><font
face="Verdana"><fontsize="-1">Firmware Engineer</font></font><p><b><font face="Verdana"><font size="-1">ENTERASYS
NETWORKS</font></font></b><p><fontface="Verdana"><font size="-1">Phone:          801 887-9888</font></font><br /><font
face="Verdana"><fontsize="-1">Fax:            801 972-5789</font></font><br /><font face="Verdana"><font
size="-1">Email:         <u><font color="#0000FF"><a
href="mailto:rhunter@enterasys.com">rhunter@enterasys.com</a></font></u></font></font><br/><font face="Verdana"><font
size="-1">www:           <u><font color="#0000FF"><a
href="http://www.enterasys.com">www.enterasys.com</a></font></u></font></font></blockquote>

hi hunter, <br />  <p>nextval increments the sequence in an atomic operation and returns the current value <br />of
thesequence.  It is transaction safe in the sense, <p>if two webserver process do two transactions simultaneously they
wouldstill get different <br />current vals. <p>so last_val should not be the approach. <p>forgive me if i am not
understanding the intricacies of your <br />problem. <br />  <br />  <p>"Hunter, Ray" wrote: <blockquote type="CITE"> 
<p><fontsize="-1">The only problem with this solution is that I have already incremented the sequence and need to use
thecurrent value?  Is there a way to assign the value to a variable and then use it in an insert
statement?</font><p><fontsize="-1">This is just a hashed out example.  I am hoping it can all be done in sql
statements...</font><br/><font size="-1">Example:</font><br />        <font size="-1">var num = select last_value from
user_table_id_seq;</font><p>       <font size="-1">insert into users ( "user_id", "user_fname", "user_lname",
"user_email")</font><br /><font size="-1">        values ( 'num', 'Ray', 'Hunter', 'rhunter@enterasys.com'
);</font><p><fontsize="-1">If I could do this is would be great...Is it possible?</font><p><font
size="-1">Thanks,</font><br/>  <br />  <p><font size="-1">Ray Hunter</font><br /><font size="-1">Firmware
Engineer</font><p><fontsize="-1">ENTERASYS NETWORKS</font><p><font size="-1">-----Original Message-----</font><br
/><fontsize="-1">From: Bruno Wolff III [<a href="mailto:bruno@wolff.to">mailto:bruno@wolff.to</a>]</font><br /><font
size="-1">Sent:Monday, February 11, 2002 7:37 AM</font><br /><font size="-1">To: Hunter, Ray</font><br /><font
size="-1">Cc:pgsql-sql@postgresql.org</font><br /><font size="-1">Subject: Re: [SQL] Sequences</font><p><font
size="-1">OnMon, Feb 11, 2002 at 08:43:23AM -0500,</font><br /><font size="-1">  "Hunter, Ray"
<rhunter@enterasys.com>wrote:</font><br /><font size="-1">> I have various sequences in my database set up for
ids. My question</font><br /><font size="-1">> is: How can I get the current value of the sequence without creating
a</font><br/><font size="-1">> session and using the currval function?</font><br /><font size="-1">></font><br
/><fontsize="-1">> I would like to take the current value of the sequence and use it for</font><br /><font
size="-1">>a value in an insert statement.</font><br /><font size="-1">></font><br /><font
size="-1">></font><br/><font size="-1">> Example:</font><br /><font size="-1">></font><br /><font
size="-1">>      insert into users ( "user_id", "user_fname", "user_lname",</font><br /><font size="-1">>
"user_email")</font><br /><font size="-1">>       values ( 'current sequence', 'Ray', 'Hunter',
'rhunter@enterasys.com'</font><br/><font size="-1">> );</font><p><font size="-1">I think you want to use nextval in
thiscontext. currval is used when you have already gotten a new sequence number and want to use it in several inserts
inthe same transaction.</font><p><font size="-1">If you make user_id a serial type, then its default value will be
nextvaland you could use: insert into users ( "user_fname", "user_lname", "user_email" )</font><p><font size="-1"> 
values('Ray', 'Hunter', 'rhunter@enterasys.com' );</font><br /><font size="-1">to add a row.</font></blockquote> 

On Mon, Feb 11, 2002 at 10:12:33AM -0500, "Hunter, Ray" <rhunter@enterasys.com> wrote:
> The only problem with this solution is that I have already incremented the
> sequence and need to use the current value?  Is there a way to assign the
> value to a variable and then use it in an insert statement?

You should be doing this in one transaction in which case currval should
work. If you aren't doing it in one transaction, then your application
will need to remember the current value.

On Mon, 2002-02-11 at 14:43, Hunter, Ray wrote:
> I have various sequences in my database set up for ids.  My question is:
> How can I get the current value of the sequence without creating a session
> and using the currval function?
>
> I would like to take the current value of the sequence and use it for a
> value in an insert statement.
>
>
> Example:
>
>     insert into users ( "user_id", "user_fname", "user_lname",
> "user_email" )
>     values ( 'current sequence', 'Ray', 'Hunter',
> 'rhunter@enterasys.com' );
>

You probably wouldn't want to use the current but the next value of the
sequence. Common solution is to set the default for the column user_id
to something like
nextval(users_user_id_seq"::text)
and omit user_id in the insert statement such that the default value is
taken.

Markus Bertheau

Cenes Data GmbH

<div>Ray,</div><div><br /></div><div>You can certainly store the value in a variable, how to do that depends on the
languageyou're using. If you're running PL/pgSQL, then you would have to define the variable somewhere on top and then
use</div><div>       </div><div>var_num := ...</div><div><br /></div><div>Best regards,</div><div>Chris</div><div><br
/></div><div>At10:12 -0500 02/11/2002, Hunter, Ray wrote:</div><blockquote cite="cite" type="cite"><font size="-1">The
onlyproblem with this solution is that I have already incremented the sequence and need to use the current value?  Is
therea way to assign the value to a variable and then use it in an insert statement?</font><br
/></blockquote><blockquotecite="cite" type="cite"><font size="-1">This is just a hashed out example.  I am hoping it
canall be done in sql statements...</font><br /><font size="-1">Example:</font><br />        <font size="-1"> var num =
selectlast_value from user_table_id_seq;</font><br /></blockquote><blockquote cite="cite" type="cite">       <font
size="-1">insert into users ( "user_id", "user_fname", "user_lname", "user_email" )</font><br /><font size="-1">       
values( 'num', 'Ray', 'Hunter', 'rhunter@enterasys.com' );</font><br /></blockquote><blockquote cite="cite"
type="cite"><br/></blockquote><blockquote cite="cite" type="cite"><font size="-1">If I could do this is would be
great...Isit possible?</font><br /></blockquote><blockquote cite="cite" type="cite"><br /></blockquote><blockquote
cite="cite"type="cite"><font size="-1">Thanks,</font><br /></blockquote><blockquote cite="cite" type="cite">      <br
/></blockquote><blockquotecite="cite" type="cite"><font size="-1">Ray Hunter</font><br /><font size="-1">Firmware
Engineer</font><br/></blockquote><blockquote cite="cite" type="cite"><font size="-1">ENTERASYS NETWORKS</font><br
/></blockquote><blockquotecite="cite" type="cite"><br /></blockquote><blockquote cite="cite" type="cite"><font
size="-1">-----OriginalMessage-----</font><br /><font size="-1">From: Bruno Wolff III [</font><a
href="mailto:bruno@wolff.to"><fontsize="-1">mailto:bruno@wolff.to</font></a><font size="-1">]</font><br /><font
size="-1">Sent:Monday, February 11, 2002 7:37 AM</font><br /><font size="-1">To: Hunter, Ray</font><br /><font
size="-1">Cc:pgsql-sql@postgresql.org</font><br /><font size="-1">Subject: Re: [SQL] Sequences</font><br
/></blockquote><blockquotecite="cite" type="cite"><br /></blockquote><blockquote cite="cite" type="cite"><font
size="-1">OnMon, Feb 11, 2002 at 08:43:23AM -0500,</font><br /><font size="-1">  "Hunter, Ray"
<rhunter@enterasys.com>wrote:</font><br /><font size="-1">> I have various sequences in my database set up for
ids. My question</font><br /><font size="-1">> is: How can I get the current value of the sequence without creating
a</font><br/><font size="-1">> session and using the currval function?</font><br /><font size="-1">></font><br
/><fontsize="-1">> I would like to take the current value of the sequence and use it for</font><br /><font
size="-1">>a value in an insert statement.</font><br /><font size="-1">></font><br /><font
size="-1">></font><br/><font size="-1">> Example:</font><br /><font size="-1">></font><br /><font
size="-1">>      insert into users ( "user_id", "user_fname", "user_lname",</font><br /><font size="-1">>
"user_email")</font><br /><font size="-1">>       values ( 'current sequence', 'Ray', 'Hunter',
'rhunter@enterasys.com'</font><br/><font size="-1">> );</font><br /></blockquote><blockquote cite="cite"
type="cite"><fontsize="-1">I think you want to use nextval in this context. currval is used when you have already
gottena new sequence number and want to use it in several inserts in the same transaction.</font><br
/></blockquote><blockquotecite="cite" type="cite"><font size="-1">If you make user_id a serial type, then its default
valuewill be nextval and you could use: insert into users ( "user_fname", "user_lname", "user_email" )</font><br
/></blockquote><blockquotecite="cite" type="cite"><font size="-1">  values ('Ray', 'Hunter', 'rhunter@enterasys.com'
);</font><br/><font size="-1">to add a row.</font></blockquote><div><br /></div><div><br /></div><pre>-- 
 
</pre><div align="center"><font color="#0000FF">Chris Ruprecht</font></div><div align="center"><font
color="#0000FF">Networkgrunt and bit pusher extraordinaíre</font></div>
_________________________________________________________Do You Yahoo!? Get your free @yahoo.com address at
http://mail.yahoo.com 

Andre Rothe <arothe@phosco.info> writes:
> Where are stored the sequence information? How I can query the
> properties of a sequence like increment, max/min value, cache?
> I'm looking for a table like user_sequences in Oracle, where I
> can query all of my sequences.

cbbrowne=# create sequence foo;
CREATE SEQUENCE
cbbrowne=# select * from foo;sequence_name | last_value | increment_by |      max_value      | min_value | cache_value
|log_cnt | is_cycled | is_called
 

---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------foo
         |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f
 
(1 row)

Each sequence is effectively a relation.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/wp.html
Where do you want to Tell Microsoft To Go Today?

Nice Information. Does somebody know how to get the complete create-statement of an existing table/view?




--
Mit freundlichen Grüßen

Dizdarevic Jasmin
Sonnenbergstr. 3
6714 Nüziders, AUT

jasmin.dizdarevic@gmail.com
+43 664 411 79 29

Easiest way is with pg_dump -s -t tablename dbname

On Sat, Jul 4, 2009 at 6:35 AM, Jasmin
Dizdarevic<jasmin.dizdarevic@gmail.com> wrote:
> Nice Information. Does somebody know how to get the complete
> create-statement of an existing table/view?
>
> 2009/7/3 Chris Browne <cbbrowne@acm.org>
>>
>> Andre Rothe <arothe@phosco.info> writes:
>> > Where are stored the sequence information? How I can query the
>> > properties of a sequence like increment, max/min value, cache?
>> > I'm looking for a table like user_sequences in Oracle, where I
>> > can query all of my sequences.
>>
>> cbbrowne=# create sequence foo;
>> CREATE SEQUENCE
>> cbbrowne=# select * from foo;
>>  sequence_name | last_value | increment_by |      max_value      |
>> min_value | cache_value | log_cnt | is_cycled | is_called
>>
>>
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
>>  foo           |          1 |            1 | 9223372036854775807 |
>> 1 |           1 |       1 | f         | f
>> (1 row)
>>
>> Each sequence is effectively a relation.
>> --
>> output = ("cbbrowne" "@" "cbbrowne.com")
>> http://www3.sympatico.ca/cbbrowne/wp.html
>> Where do you want to Tell Microsoft To Go Today?
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Mit freundlichen Grüßen
>
> Dizdarevic Jasmin
> Sonnenbergstr. 3
> 6714 Nüziders, AUT
>
> jasmin.dizdarevic@gmail.com
> +43 664 411 79 29
>
>



--
When fascism comes to America, it will be intolerance sold as diversity.

Thanks, I have used

SELECT sequence_name FROM information_schema.sequences WHERE 
sequence_catalog=? AND sequence_schema=?

and then I can use

select * from <sequence-name>

It seems to work :-)
Andre

Chris Browne wrote:
> Andre Rothe <arothe@phosco.info> writes:
>> Where are stored the sequence information? How I can query the
>> properties of a sequence like increment, max/min value, cache?
>> I'm looking for a table like user_sequences in Oracle, where I
>> can query all of my sequences.
> 
> cbbrowne=# create sequence foo;
> CREATE SEQUENCE
> cbbrowne=# select * from foo;
>  sequence_name | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled |
is_called
>
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
>  foo           |          1 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f
> (1 row)
> 
> Each sequence is effectively a relation.