Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child
tables. When I do the INSERT in the parent table, I
have an ID (generated) by the sequence (SERIAL field),
and I have to use this ID to reference all child
tables.
Well, once I do an INSERT in the parent table, how can
I know (for sure) which number id was generated by the
sequence?
Simple example:
------------------------------------------------------
CREATE TABLE parent(id SERIAL, descrip CHAR(50));
------------------------------------------------------
So,
------------------------------------------------------
INSERT INTO parent (descrip) VALUES ('project 1');
------------------------------------------------------
How can I now (for sure) with value was generated by
the sequence to fill the field ID?
(There is lots of users using the software at the same
time, so I am not able to use the last_value()
function on the sequence.)
Best Regards,
Marcelo Pereira
Brazil
_______________ _______________ _______________ __________
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 10 6388
I think the best way would be not to use a SERIAL field, but an INTEGER
field and a sequence:
CREATE SEQUENCE parent_seq;
CREATE TABLE parent(id INTEGER, descrip CHAR(50));
So when you want to insert on the parent table, you obtain the next
value from the sequence and then you insert in the parent and child
tables the value you obtained:
newId:=SELECT nextval('parent _seq')
INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX');
INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);
hope it helps.
MaRCeLO PeReiRA wrote: Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence (SERIAL field), and I have to use this ID to reference all child tables.
Well, once I do an INSERT in the parent table, how can I know (for sure) which number id was generated by the sequence?
Simple example:
------------------------------------------------------ CREATE TABLE parent(id SERIAL, descrip CHAR(50)); ------------------------------------------------------
So,
------------------------------------------------------ INSERT INTO parent (descrip) VALUES ('project 1'); ------------------------------------------------------
How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Best Regards,
Marcelo Pereira Brazil
______________ _______________ _______________ ___________ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
On Thu, 2004-11-11 at 09:59 -0300, MaRCeLO PeReiRA wrote: Hi guys, I am in troubles with a SERIAL field. I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence (SERIAL field), and I have to use this ID to reference all child tables. Well, once I do an INSERT in the parent table, how can I know (for sure) which number id was generated by the sequence? Simple example: ------------------------------------------------------ CREATE TABLE parent(id SERIAL, descrip CHAR(50)); ------------------------------------------------------ So, ------------------------------------------------------ INSERT INTO parent (descrip) VALUES ('project 1'); ------------------------------------------------------ How can I now (for sure) with value was generated by the sequence to fill the field ID? (There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.) Best Regards, Marcelo Pereira Brazil
I just asked this same question about a week or two ago and I got a
response from Jonathan Daugherty who helped me with the initial query,
and in PHP I was able to come up with: http://blog.planetargon.com/index.ph...nsert_id_.html
This was on the list a few weeks ago:
-- get_sequence(sc hema_name, table_name, column_name) CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS text AS ' SELECT seq.relname::te xt FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, pg_depend WHERE pg_depend.refob jsubid = pg_attribute.at tnum AND pg_depend.refob jid = src.oid AND seq.oid = pg_depend.objid AND src.relnamespac e = pg_namespace.oi d AND pg_attribute.at trelid = src.oid AND pg_namespace.ns pname = $1 AND src.relname = $2 AND pg_attribute.at tname = $3; ' language sql;
hth,
Robby
--
/*************** *************** *********
* Robby Russell | Owner.Developer .Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetarg on.com
* 503.351.4730 | blog.planetargo n.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 ---
*************** *************** **********/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
iD8DBQBBk6vN0Qa QZBaqXgwRAiK0AJ 9B6QPP/fjeHe4PoCqAFkss se6x4ACg3ad6
gEQQ4QsuSQ8UGXO KPKc+8+4=
=Xn7A
-----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
option 1) use a stored procedure to create the record. Within a transaction
the last value function will return the correct last value, not the one of a
concurrent insert.
option 2) if you know that this user uses the same connection for all his
queries - or at least the ones in question - you can rely on the last value
being the correct one. Like with transactions, if you use the same connection
the last value will be the correct one.
You're only in trouble if you're not within a transaction and you're not sure
if the connection stays the same for the queries in question. The later could
be due to connection pooling.
UC
On Thursday 11 November 2004 04:59 am, MaRCeLO PeReiRA wrote: Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence (SERIAL field), and I have to use this ID to reference all child tables.
Well, once I do an INSERT in the parent table, how can I know (for sure) which number id was generated by the sequence?
Simple example:
------------------------------------------------------ CREATE TABLE parent(id SERIAL, descrip CHAR(50)); ------------------------------------------------------
So,
------------------------------------------------------ INSERT INTO parent (descrip) VALUES ('project 1'); ------------------------------------------------------
How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Best Regards,
Marcelo Pereira Brazil
_______________ _______________ _______________ __________ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discadoragora! http://br.acesso.yahoo.com/
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD4DBQFBk6lnjqG XBvRToM4RAgOXAJ iVy9TG9Yv05pegz ACw2VPeN7USAKDR Yg/N
H0BKK8WT1aOZ+CB 3rCl8WQ==
=kiLq
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Thu, Nov 11, 2004 at 09:59:16 -0300,
MaRCeLO PeReiRA <ga********@yah oo.com.br> wrote: Well, once I do an INSERT in the parent table, how can I know (for sure) which number id was generated by the sequence?
Use currval.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
MaRCeLO PeReiRA wrote: How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Yes you are nextval()/currval() are multi-user safe. They return the
next/current value *in the current connection*.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Which means that sometimes they do not return the correct value - if you
have a trigger that inserts another record you will not get the right value.
MSSQL has @@IDENTITY and SCOPE_IDENTITY( ) to handle this case, I'm new to
pgsql so I don't know if it has anything like that.
Jerry
"Richard Huxton" <de*@archonet.c om> wrote in message
news:41******** ****@archonet.c om... MaRCeLO PeReiRA wrote:
How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Yes you are nextval()/currval() are multi-user safe. They return the next/current value *in the current connection*.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 11 November 2004 10:23 am, Franco Bruno Borghesi wrote: I think the best way would be not to use a SERIAL field, but an INTEGER field and a sequence:
a "serial" is just a convenient shortcut to an int with an automatically
created sequence. As proof - just create a table with a serial and dump it
with pg_dump: you'll end up with a table containing an int with a nextval(....
as the default. The only difference is that in case of the "serial" field you
don't name the sequence yourself.
CREATE SEQUENCE parent_seq; CREATE TABLE parent(id INTEGER, descrip CHAR(50));
So when you want to insert on the parent table, you obtain the next value from the sequence and then you insert in the parent and child tables the value you obtained:
newId:=SELECT nextval('parent _seq') INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX'); INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId); INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId); INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);
which amounts to the curval in the same connection. hope it helps.
MaRCeLO PeReiRA wrote:Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence (SERIAL field), and I have to use this ID to reference all child tables.
Well, once I do an INSERT in the parent table, how can I know (for sure) which number id was generated by the sequence?
Simple example:
------------------------------------------------------ CREATE TABLE parent(id SERIAL, descrip CHAR(50)); ------------------------------------------------------
So,
------------------------------------------------------ INSERT INTO parent (descrip) VALUES ('project 1'); ------------------------------------------------------
How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Best Regards,
Marcelo Pereira Brazil
______________ _______________ _______________ ___________ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFBlFD5jqG XBvRToM4RAmfQAJ 9JyQxERqcau1kCn vkrXNmpaGTwzwCg qK6L
7zCpR+uO5pzvDuY/itTYCfs=
=mq0M
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
--- Jerry III <je******@hotma il.com> wrote: Which means that sometimes they do not return the correct value - if you have a trigger that inserts another record you will not get the right value.
If you are new to PostgreSQL, as you say, then why are
you so sure of this? Perhaps you may profit from
looking a little more at how currval() works.
MSSQL has @@IDENTITY and SCOPE_IDENTITY( ) to handle this case, I'm new to pgsql so I don't know if it has anything like that.
Jerry
"Richard Huxton" <de*@archonet.c om> wrote in message
news:41******** ****@archonet.c om... MaRCeLO PeReiRA wrote:
How can I now (for sure) with value was generated by the sequence to fill the field ID?
(There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.)
Yes you are nextval()/currval() are multi-user safe. They return the next/current value *in the current connection*.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
_______________ _______________ ____
Do you Yahoo!?
The all-new My Yahoo! - Get yours free! http://my.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Tue, 16 Nov 2004, Jeff Eckermann wrote: --- Jerry III <je******@hotma il.com> wrote:
Which means that sometimes they do not return the correct value - if you have a trigger that inserts another record you will not get the right value.
If you are new to PostgreSQL, as you say, then why are you so sure of this? Perhaps you may profit from looking a little more at how currval() works.
He's correct. One thing that currval will not help with is a
case where more than one row has been inserted by a statement
(whether due to the base statement or triggers).
A somewhat absurd example:
---
create table q1(a serial, b int);
create function f1() returns trigger as 'begin if (random() >
0.5) then insert into q1 default values; end if; return NEW; end;'
language 'plpgsql';
create trigger q1_f1 after insert on q1 for each row execute
procedure f1();
insert into q1(b) values (3);
select currval('q1_a_s eq');
select * from q1;
----
I got a currval of 3 which was the last row inserted, but that was from
the trigger, not the row created by my insert so it didn't have the
correct b value.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: PT |
last post by:
I got a problem. And thats.....
First of all, I got these three tables.
------------------- ------------------ ----------------------
tblPerson tblPersonSoftware tblSoftware
------------------- ------------------ ----------------------
PID PName PID* SID* SID SWName
--- ...
|
by: Carlos San Miguel |
last post by:
Hi, I'm going to be using Oracle with a customer. I will access
Oracle with ODBC. All the tables have an Identity column, this is the
ID for the record. I need to know how I can get the value of the
Identity column for the last record inserted. A stored procedure maybe?
TIA
Carlos San Miguel
|
by: Ann |
last post by:
Hello everyone, hope someone can help me with this.
I have a SQL stored procedure that inserts a record into a table,
creates a cursor to fetch the last record that was added to get the
unique key that was created and then writes that and other info to a
separate table. This procedure was working fine at our ISP under NT 4
and SQL 7.
We...
|
by: mgarriss |
last post by:
Given this table:
CREATE TABLE test ( id SERIAL, example TEXT );
An implicit sequence is created as show in this message:
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
SERIAL column 'test.id'
How do I retrieve the last 'id' that was inserted? I have a process
|
by: Maurizio Faini |
last post by:
I have a little problem.
there is a way to get last id inserted into db or i have to make a new query?
I explain better my question:
in vbscript using sqlserver2000 i can use this code:
varBookmark=rs.Bookmark
rs.Update
| |
by: rodrigo |
last post by:
How to get get the last inserted key (value NOT column) in a specific
dataTable dt right after dt.Rows.InsertAt according to code and schema
below. The key is a autoincrement column.
foreach (MyContainer item in al)
{
|
by: Veeru71 |
last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?
I can't use identity_val_local() as the INSERTS are happening in a
different session.
Eg, We have the following table....
|
by: Daz |
last post by:
Hi.
I would like to know how to obtain a reference to (or at least, element
type of) the last node which was inserted into the document. I am using
an event listener to listen for dom inserts, but I am not sure how to
check if that last node was a script tag.
Please could someone point me in the right direction?
Many thanks, and Merry...
|
by: imranabdulaziz |
last post by:
Dear all,
i am using asp.net ,C# (VS 2005) and sql server 2005.
i have written sp for inserting the the data which written last inserted idendity no.
i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form.
please guide me.
thanks
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |