Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri Nov 16, 2018 4:45 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Insert into with generator in PostgreSQL
PostPosted: Wed Jan 09, 2013 1:26 am 
Offline

Joined: Sun Jan 22, 2006 2:37 am
Posts: 327
Hi,

I have a generator in PostgreSQL that I call each time I need a new rowid for my table.

However in this case I want to do an insert in the table with select that will generate multiple records to be inserted in the one query, so I need the generator to fire for each one of those records so they all have unique ids.

My generator is called by:

SELECT nextval('mytable_rowid')

So in the query I was trying to use as the find criteria:

SELECT
(SELECT nextval('mytable_rowid')),
region,
FROM
mytable
WHERE
invoice IS NULL

But I just get the same row id for each of the 20 or so found records. Is there a way of doing this so I get a unique id for each record thats going to be inserted from the generator?

Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: Insert into with generator in PostgreSQL
PostPosted: Wed Jan 09, 2013 10:01 am 
Offline

Joined: Fri Aug 31, 2012 3:56 pm
Posts: 82
1. Use Serial or BigSerial Datatype. See: http://www.postgresql.org/docs/9.2/inte ... YPE-SERIAL

2. SELECT only find the Records, use UPDATE to change. See: http://www.postgresql.org/docs/9.2/inte ... pdate.html


Top
 Profile  
Reply with quote  
 Post subject: Re: Insert into with generator in PostgreSQL
PostPosted: Thu Jan 17, 2013 7:24 am 
Offline

Joined: Sun Jan 22, 2006 2:37 am
Posts: 327
Hi,

Thanks and I got it working and I just took the select out of the brackets inside the insert and it provided a new id per row it inserted!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group