i need an advice on postgres keys

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

i need an advice on postgres keys

Post by pedrotuga »

hey all. i usualy use mysql, this time i need to develop a website using postgres.

Usually i set the key to autoincrement in mysql. It appears that postgres doesnt have such a thing.

How do u deal with inserts? do you query the db to get the highest key value and then increment it with php?

what other method can i use?
thx.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Postgresql does have such a feature, it's called "sequence". (Now look that up in their manual)
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

Post by pedrotuga »

mmm... i see.. its a tricky approach i think. Mysql way is more simplifyed IMO.

I set the keys to serial and it will create an autosequence then i cn use next() corrent() and prev().

It took me quite some time searching and readind docs about this... postgres is not so well documented as mysql at all.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

This was interesting: http://www.faqts.com/knowledge_base/vie ... 204/fid/16

I've done it before but this looks familiar. I don't use nextval() or anything on my queries. I believe it actually auto-increments without needing to send it a custom function.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Anyway, i meant SERIAL ;)

Code: Select all

CREATE TABLE joined (
  joined_id SERIAL, 
  firstname CHAR(20) NOT NULL, 
  lastname CHAR(30) NOT NULL, 
  PRIMARY KEY(joined_id)
)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

timvw wrote:Anyway, i meant SERIAL ;)

Code: Select all

CREATE TABLE joined (
  joined_id SERIAL, 
  firstname CHAR(20) NOT NULL, 
  lastname CHAR(30) NOT NULL, 
  PRIMARY KEY(joined_id)
)
I did wonder ;) I thought I was going mad for a minute earlier.... don't often have to question a remark from your good self :P
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

<off-topic>
You no hablo pgsql ;) The first post was hasty, by heart @ work during lunchbreak.. The second was when i got home and looked in my code..
</off-topic>

I did remember that i searched the web for "postgresql autoincrement sequence" once and that it didn't take long to find what i needed...
Post Reply