By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

Get the generated value of serial type field (primary key)

P: 66
Expand|Select|Wrap|Line Numbers
  1. -- Table: public.customers
  2. CREATE TABLE public.customers (
  3.   customercd   serial NOT NULL,
  4.   pass         char(32) NOT NULL,
  5.   lnamek       varchar(20) NOT NULL,
  6.   fnamek       varchar(20) NOT NULL,
  7.   status       boolean NOT NULL DEFAULT true,
  8.   /* Keys */
  9.   CONSTRAINT customers_pkey
  10.     PRIMARY KEY (customercd)
  11. ) WITHOUT OIDS;
  12.  
  13. CREATE INDEX customers_index01
  14.   ON public.customers
  15.   (customercd, status);
Expand|Select|Wrap|Line Numbers
  1. -- Table: public.customercontacts
  2. CREATE TABLE public.customercontacts (
  3.   customercd  integer NOT NULL,
  4.   contact     varchar(50) NOT NULL,
  5.   "type"      varchar(3),
  6.   /* Keys */
  7.   CONSTRAINT customercontacts_pkey
  8.     PRIMARY KEY (contact),
  9.   /* Foreign keys */
  10.   CONSTRAINT foreign_key01
  11.     FOREIGN KEY (customercd)
  12.     REFERENCES public.customers(customercd)
  13.     ON DELETE RESTRICT
  14.     ON UPDATE CASCADE
  15. ) WITHOUT OIDS;
1) insertCustomer(pass, fnamek, lnamek)
-- no need to provide the customercd and status because auto-increment and with default value respectively.

2) insertCustomerContact(customercd, contact, type)
-- need to provide the all fields.

Problem: How can I determine the previously used customercd that generated by the serial field upon insertCustomer in order to pass the customercd as argument to insertCustomerContact.
Aug 10 '07 #1
Share this Question
Share on Google+
6 Replies


P: 66
I found something that may be the solution..but please confirmed by the expert...

This is safe to use? That the value of last_value field from customers_customercd_seq is always the previously used/added upon insert?
Expand|Select|Wrap|Line Numbers
  1. insert into customercontacts (customercd, contact, 'type')
  2. values (select last_value from customers_customercd_seq,'09084789545', 'MOB');
BUT error occurred Error:
ERROR: syntax error at or near "'type'";
Error while executing the query (State:42601, Native Code: 7)



When I removed the single quote ( ' ) :
Expand|Select|Wrap|Line Numbers
  1. insert into customercontacts (customercd, contact, type)
  2. values (select last_value from customers_customercd_seq,'09084789545', 'MOB');
also error occurred:
Error: ERROR: syntax error at or near "select";
Error while executing the query (State:42601, Native Code: 7)


I am using WinSQL via ODBC
Aug 10 '07 #2

Expert 100+
P: 534
Try this:
Expand|Select|Wrap|Line Numbers
  1. insert into customercontacts (customercd, contact, type)
  2. values 
  3. (select last_value, '09084789545', 'MOB' from customers_customercd_seq,)
  4.  
Aug 10 '07 #3

Expert 100+
P: 534
As far as getting the last auto number getting it off the last_value field is not the best option. This field shows the last value allocated by any session, meaning that if you have concurrent or nearly concurrent processes you may get the wrong value. I would recommend using the currval function instead.

Generally speaking having to obtain the previously auto generated number is a red flag.
If you are doing some class project it's ok, but if you are working on a real life application we may need to consider other options.
Aug 10 '07 #4

P: 66
As far as getting the last auto number getting it off the last_value field is not the best option. This field shows the last value allocated by any session, meaning that if you have concurrent or nearly concurrent processes you may get the wrong value. I would recommend using the currval function instead.

Generally speaking having to obtain the previously auto generated number is a red flag.
If you are doing some class project it's ok, but if you are working on a real life application we may need to consider other options.
What's the other options? It is currval function?
Aug 13 '07 #5

Expert 100+
P: 534
What's the other options? It is currval function?
No, currval is the first option - getting value directly from the last_value field is simply a bad and unsafe approach.
The alternative would be to create the sequence table manually, not associating it with your key field on the data table.
In this case you will be responsible for getting the next value from the sequence and using it to insert the row in your table.
This is a trade-off, and you would need to weight pros and cons.
With what you have now everything is automated - you don't have to worry about getting the next key, or maintaining the sequence - if you drop the table the sequence gets dropped, if you create the table the sequence gets created.
But you have to do something to obtain the key of your record, which is from the purist point of view pretty bad.
If you manage the keys manually you don't have to do anything to find its value - you have it; but on the other hand you are responsible for the whole business of maintaining the sequence table and getting the values from there.
I had experience with both of this techniques, there's no "better" way, but there is a way that could be better for a particular situation.
Follow these links for more information:
http://www.postgresql.org/docs/8.2/s...esequence.html
http://www.postgresql.org/docs/8.2/s...-sequence.html
Aug 14 '07 #6

P: 66
No, currval is the first option - getting value directly from the last_value field is simply a bad and unsafe approach.
The alternative would be to create the sequence table manually, not associating it with your key field on the data table.
In this case you will be responsible for getting the next value from the sequence and using it to insert the row in your table.
This is a trade-off, and you would need to weight pros and cons.
With what you have now everything is automated - you don't have to worry about getting the next key, or maintaining the sequence - if you drop the table the sequence gets dropped, if you create the table the sequence gets created.
But you have to do something to obtain the key of your record, which is from the purist point of view pretty bad.
If you manage the keys manually you don't have to do anything to find its value - you have it; but on the other hand you are responsible for the whole business of maintaining the sequence table and getting the values from there.
I had experience with both of this techniques, there's no "better" way, but there is a way that could be better for a particular situation.
Follow these links for more information:
http://www.postgresql.org/docs/8.2/s...esequence.html
http://www.postgresql.org/docs/8.2/s...-sequence.html
Yes, I realized that you are strongly correct. I need to have a table that maintain all the keys.

On the case of manually maintain keys in the table, I can also automate the generation of keys based on the table.

Thank you very much.
Aug 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.