How to use Sequence to generate primary key

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Nieuwsma
Posts: 9
Joined: Wed 05 May 2010 16:30

How to use Sequence to generate primary key

Post by Nieuwsma » Wed 05 May 2010 18:43

I'm using dotConnect's LINQ. Our postgres db uses sequences to generate unique primary integer keys. How do I get the nextval from a sequence? I cannot use ExecuteCommand since it doesn't return the new value. Please provide an example.
Thanks for your help.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 06 May 2010 15:19

Here is a simple way:
1. Create a trigger in DB that will be populating the autoincrement field.
2. Set Auto Generated Value to true for the autoincrement PK column.
3. Set the AutoSync property to the OnInsert.
This will do the trick - the PK values will be populated after inserting.

Nieuwsma
Posts: 9
Joined: Wed 05 May 2010 16:30

Anyway without trigger?

Post by Nieuwsma » Thu 06 May 2010 15:31

Thanks for the response, AndreyR.

We have other code that uses ODBC to work with the database. It would all need to be changed to use the trigger. Is there a way to use sequences without changing the database?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 07 May 2010 11:49

Try defining the column in the following way:

Code: Select all

id integer DEFAULT nextval('autoinc_seq'::regclass)
Set the properties like I have described in the previous post, this should help.
Please let me know if anything goes wrong.

Post Reply