- -- 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) 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.
6 6240
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? - 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
Try this: -
insert into customercontacts (customercd, contact, type)
-
values
-
(select last_value, '09084789545', 'MOB' from customers_customercd_seq,)
-
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.
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?
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paul Lamonby |
last post by:
Hi,
i want to create a unique serial number to my Db entries. I thought
the best way would be to add the auto_increment primary key value to a
string, then insert it into a table field...
|
by: Marek Lewczuk |
last post by:
I'm curious if the default scheme for sequence name (which is created
with SERIAL data type) can be changed -- currently all sequences are
named like this: <table_name>_<field_name>_seq -- can it...
|
by: Poul Møller Hansen |
last post by:
Hi,
I need an auto incrementing field that will contain values like
N000001, N000002, N000003 etc.
I think the way is to use the value from an identity field in a stored
procedure that is...
|
by: Prabu Subroto |
last post by:
Dear my friends...
I am using postgres 7.4 and SuSE 9.1.
I want to use auto_increment as on MySQL. I look up
the documentation on www.postgres.com and I found
"serial" .
But I don't know...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: bill |
last post by:
In an application I am writing the user can define a series of
steps to be followed. I save them in a sql database using the
field "order" (a smallint) as the primary key.
(there are in the range...
|
by: Andrus |
last post by:
I need to create generic table field level cache.
Table primary key (PrimaryKeyStructType) can be int, string or struct
containing int and string fields.
FieldName contains table field name to be...
|
by: Elfae |
last post by:
I have searched high and low for a sample for this, and I just can't
find any. Sorry for the length!
Background Information
The issue revolves around setting up a system-generated increase in...
|
by: Frank Swarbrick |
last post by:
We're trying to take advantage of the new ROW CHANGE TIMESTAMP option.
Here is a simple table:
CREATE TABLE "ACCTASGN"."NUMBER_STATUS" (
"STATUS_CODE" CHAR(1) NOT NULL ,
"STATUS_DESCRIPTION"...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |