Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Sep 21, 2019 2:57 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: PostgreSQL Primary Key Generation
PostPosted: Wed Oct 03, 2012 8:34 pm 
Offline

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

In PostgreSQL can I get a new primary key id to use for a new record, or do I need to actually create a record first before I know its primary key id?

Then if the user decides not to save the record, do I have to then delete that new record so I don't have blank records in the database?

Is there any other safe way to generate a new primary key id record for a new record?


Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Fri Oct 05, 2012 9:23 am 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
I'm going to assume you're talking about creating an auto-incremented column to be used as the Primary Key. The answer is, then, of course. :)

In Pg you can use the serial (or bigserial, if you want an 8 byte integer) numeric type to create an auto-incremented column. What this actually does is creates an integer column, simultaneously creates a Sequence, and sets the DEFAULT of the column to the NextVal('sequence_name').

This may seem like extra work (seeing as in MySQL you can just set an auto-increment property), but this pulls the magic to the surface, allowing for more capability. You could, for example, use the sequence for more than one index across multiple tables. You can also determine the step size.

There are also functions that go along with Sequences so you can check the last, current, and next value. If you use the functions, you don't have to worry about creating a new row in order to find out what the new value is.

Hope this helps! :)

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Fri Oct 05, 2012 11:32 pm 
Offline

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

Thats absolutely perfect and it works great! I just found the Max(_rowid) + 1 of the table and then added a sequence for that table with that number as the starting value. Now each time I want to find a NEWID, instead of using NEWID, I just replaced the SQL with SELECT nextval('serial'). It works great!

Thank you very much for the great advice!

Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Sat Oct 06, 2012 3:09 am 
Offline

Joined: Tue Feb 06, 2007 11:34 pm
Posts: 192
Location: Austin, TX
A GREAT many database problems go away if you switch to using UUIDs (essentially long random strings) as primary keys.

Read up on them if you want to do this, as there are caveats, and I don't know if there is a high quality UUID library for REALbasic.

_________________
Read my book, Real OOP with REALbasic
Guyren G Howe
Relevant Logic LLC

guyren-at-relevantlogic.com ~ http://relevantlogic.com

REALbasic, PHP, Ruby/Rails, Python programming
PostgreSQL, MySQL database design and consulting
Technical writing and training


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Oct 10, 2012 10:06 pm 
Offline

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

I have another question relayed to this. I have been using the generators and they work great, however how would I increment them and insert them into records if I am doing multiple inserts at once?

Here is my code:

INSERT INTO ProductBalances (model,region,_rowid) SELECT 'Productname',region,(SELECT nextval('pricelist_newid')) FROM Distributors

I have about 5 records in the distributors table and I want to insert into the product balances table. However the _rowid column is not unique when I do it, so I tried adding the generator, but it only updates once for all the items so the _rowid for all the rows being inserted is the same and so thats also invalid.

I am not sure how to generate a new id from the generator for each separate insert? Does anyone have any idea how I could do this?

Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Oct 10, 2012 10:33 pm 
Offline
Real Software Engineer

Joined: Sat Dec 24, 2005 8:18 pm
Posts: 7858
Location: Canada, Alberta, Near Red Deer
Set that columns default value to be based on the sequence
Then dont insert a value for it unless you're going to need that value in some other sub record or detail

_________________
Norman Palardy (Real Software)


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Thu Oct 11, 2012 12:27 am 
Offline

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

I did not know that could be done and how is that done? When the table is created? Sounds like I should always do that to make sure I get a unique query every time?

Grant


Last edited by Grant on Wed Nov 21, 2012 4:19 pm, edited 1 time in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Thu Oct 11, 2012 9:35 am 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
You can alter a pre-existing column:

ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT nextval('sequencename'::regclass);

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Nov 21, 2012 4:19 pm 
Offline

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

Worked great and thanks!


Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Nov 21, 2012 7:18 pm 
Offline

Joined: Tue Feb 06, 2007 11:34 pm
Posts: 192
Location: Austin, TX
If you're doing things that require you to get data back from the database (eg you need back the primary keys of the rows you just inserted), you can use a returning clause.

But you actually seldom need to do that, if you really get into the wonders that Postgres provides. If you need to chain the results of some SQL queries to use in executing others (typically, you need to use primary keys you get back from one set of inserts as foreign keys in another), you can generally do the whole set in one SQL statement if you make use of Common Table Expressions.

CTEs are useful all across work with a database (they are a better replacement for complex nested queries), and in my experience, most SQL users, even fairly experienced ones, don't know about them.

Oh, and they give you a way of doing recursive queries and other such cool things as well.

And since they let you do all sorts of things in single SQL statements rather than going back and forth to the server, CTE-based solutions are generally an order of magnitude faster.

_________________
Read my book, Real OOP with REALbasic
Guyren G Howe
Relevant Logic LLC

guyren-at-relevantlogic.com ~ http://relevantlogic.com

REALbasic, PHP, Ruby/Rails, Python programming
PostgreSQL, MySQL database design and consulting
Technical writing and training


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Nov 21, 2012 8:22 pm 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
Location: NotEvenOnTheMap, CT
Do NOT use the MAX(id)+1 trick. You can run into collisions. Use the nextval and currval functions. Nextval will increment and return the value, regardless of the transaction. So you can do multiple inserts in one transaction simply using nextval. But previous posters are correct, set the default of the column to the nextval function, and leave the column off your insert.

Use currval when you need to insert values into multiple related tables, such as:

INSERT INTO customers (name) VALUES ('Thom McGrath');
INSERT INTO email_addresses (customer_id,address) VALUES (currval(customers_id_seq),'hahaha@spammers.com');

_________________
Thom McGrath - @tekcor
Web Framework Architect, Real Software, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Wed Nov 21, 2012 8:30 pm 
Offline

Joined: Tue Feb 06, 2007 11:34 pm
Posts: 192
Location: Austin, TX
Thom McGrath wrote:
INSERT INTO customers (name) VALUES ('Thom McGrath');
INSERT INTO email_addresses (customer_id,address) VALUES (currval(customers_id_seq),'hahaha@spammers.com')
Or use a single CTE statement, something like:
WITH inserted AS (
INSERT INTO customers (name) VALUES ('Thom McGrath') RETURNING id)
INSERT INTO email_addresses (customer_id,address) VALUES ((SELECT id FROM inserted LIMIT 1),'hahaha@spammers.com')

_________________
Read my book, Real OOP with REALbasic
Guyren G Howe
Relevant Logic LLC

guyren-at-relevantlogic.com ~ http://relevantlogic.com

REALbasic, PHP, Ruby/Rails, Python programming
PostgreSQL, MySQL database design and consulting
Technical writing and training


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Thu Nov 22, 2012 8:24 pm 
Offline

Joined: Fri Jan 05, 2007 12:21 pm
Posts: 151
Location: Scotland
gisborne wrote:
A GREAT many database problems go away if you switch to using UUIDs (essentially long random strings) as primary keys.

Read up on them if you want to do this, as there are caveats, and I don't know if there is a high quality UUID library for REALbasic.


Type 4 UUIDs are random, Type 1 (Time based) or Type 3 (Hash based) are safer as database keys, less risk of collision (see 'Birthday paradox' for more details.

There is also argument on both sides whether to use an integer or UUID as primary key: Postgresql can index UUIDs pretty well, some databases aren't so efficient. In some cases there is an argument for using both, obviously only one is "primary key", but the other can be a pseudo primary key for queries.

As to a library, in the ARBP repository you'll find http://www.arbpmembers.org/source-code-repository/Networks--and--Internet/UUID-Generator/ which is a BSD style licenced class supporting three types of UUID. As author I leave it to you to decide how "high quality" it is :-)


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Fri Nov 23, 2012 12:58 am 
Offline

Joined: Tue Feb 06, 2007 11:34 pm
Posts: 192
Location: Austin, TX
s-copinger wrote:
\Type 4 UUIDs are random, Type 1 (Time based) or Type 3 (Hash based) are safer as database keys, less risk of collision (see 'Birthday paradox' for more details.]

Truly random UUIDs have essentially zero chance of a collision; that's the whole point of the exercise. If you have a really good source of random numbers, you should clearly just use random UUIDs and relax about half of the problems you have with distributed information.
The only sort of problem we have is that often don't actually have a good source of random values, and then we get into philosophical and technical debates about whether particular combinations of timestamps and whether you're standing on your left or your right foot counts as sufficiently random.

_________________
Read my book, Real OOP with REALbasic
Guyren G Howe
Relevant Logic LLC

guyren-at-relevantlogic.com ~ http://relevantlogic.com

REALbasic, PHP, Ruby/Rails, Python programming
PostgreSQL, MySQL database design and consulting
Technical writing and training


Top
 Profile  
Reply with quote  
 Post subject: Re: PostgreSQL Primary Key Generation
PostPosted: Fri Nov 23, 2012 7:43 pm 
Offline

Joined: Fri Jan 05, 2007 12:21 pm
Posts: 151
Location: Scotland
gisborne wrote:
Truly random UUIDs have essentially zero chance of a collision; that's the whole point of the exercise. If you have a really good source of random numbers, you should clearly just use random UUIDs and relax about half of the problems you have with distributed information.
The only sort of problem we have is that often don't actually have a good source of random values, and then we get into philosophical and technical debates about whether particular combinations of timestamps and whether you're standing on your left or your right foot counts as sufficiently random.


“Anyone who attempts to generate random numbers by deterministic means is, of course, living in a state of sin.” John von Neumann :D

A Type 4 UUID has 2^122 possible values, so assuming a "truly random" number generation, calling 2 has a 1 in 2^122 chance of generating the same value. For each additional number generated in a series the probability increases. Using Type 1 gives a zero chance of collision (providing the same machine is generating all the numbers) since it is time based.

Therefore with the choice between a miniscule chance and a zero chance of collision, that latter would seem the best especially if dealing with any kind of financial database. Why create a problem that doesn't need to exist?

Plus of course there may be cases where the time element of a Type 1 UUID is a useful factor in itself as a form of metadata for creation time of the record (especially if transported from one database to another, when of course it will be necessary to check that no collision exists from two seperate UUID generators coincidentally having created a UUID at _precisely_ the same time).

Of course there may be circumstances where having the time data cause some sort of data protection risk, in which case another option is needed - in which case one may want to revert to the serial integer primary key, which is where we came in... :wink:


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 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