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

[eros]

-- Table: public.customers
CREATE TABLE public.customers (
  customercd   serial NOT NULL,
  pass         char(32) NOT NULL,
  lnamek       varchar(20) NOT NULL,
  fnamek       varchar(20) NOT NULL,
  status       boolean NOT NULL DEFAULT true,
  /* Keys */
  CONSTRAINT customers_pkey
    PRIMARY KEY (customercd)
) WITHOUT OIDS;

CREATE INDEX customers_index01
  ON public.customers
  (customercd, status);

-- Table: public.customercontacts
CREATE TABLE public.customercontacts (
  customercd  integer NOT NULL,
  contact     varchar(50) NOT NULL,
  "type"      varchar(3),
  /* Keys */
  CONSTRAINT customercontacts_pkey
    PRIMARY KEY (contact),
  /* Foreign keys */
  CONSTRAINT foreign_key01
    FOREIGN KEY (customercd)
    REFERENCES public.customers(customercd)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) WITHOUT OIDS;

1) insertCustomer(pass, fnamek, lnamek)
-- no need to provide the customercd and status because auto-increment and with default value respectively.

2) insertCustomerC ontact(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 insertCustomerC ontact.

[eros]

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_custo mercd_seq is always the previously used/added upon insert?

insert into customercontacts (customercd, contact, 'type')
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 ( ' ) :

insert into customercontacts (customercd, contact, type)
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

[michaelb]

Try this:
[CODE=sql]
insert into customercontact s (customercd, contact, type)
values
(select last_value, '09084789545', 'MOB' from customers_custo mercd_seq,)
[/CODE]

[michaelb]

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.

[eros]

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?

[michaelb]

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/static/sql-createsequence.html

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

[eros]

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/static/sql-createsequence.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.