|
|
Posted by Jason Wong on 07/01/05 00:07
On Friday 01 July 2005 02:55, Uroš Kristan wrote:
> I have an application in production, build on mysql database.
>
> I decided to migrate to postgres because of numerous reasons.
Good idea :)
> Can you guys please guide me into the right direction?
>
> the main problem is the missing autoincrement of pgsql and getting the
> last record from the tabel, for linking to another tabel.
>
> How do you deal with that?
The basic idea is that you use sequences which in postgresql are the
"equivalent" of autoincrement in mysql.
Something like:
INSERT INTO category (category_id, category_name, category_description)
VALUES (nextval('category_id_seq'),
"new_category_name",
"new_category_description");
here 'category_id_seq' is the name of the sequence that produces the
unique IDs for your category_id.
To use your newly created category_id in another table:
INSERT INTO product (product_id, product_name, product_description,
category_id)
VALUES (nextval('product_id_seq'),
"new_product_name",
"new_product_description",
currval('category_id_seq'));
nextval() and currval() are native postgresql functions which operate on
sequences. Sequences are created automatically when you define a field to
be of type 'serial'.
If you need get the actual value of the newly created category_id for use
in php then you would have to do a select query, eg:
select currval('category_id_seq') as new_category_id;
and do the usual pg_query() and pg_fetch_*() to process the result
> also, can you please recommend me some good manual, explanation or book
> to help me with this problem.
Lookup "serial types" and "sequences" in the (postgresql) manual for the
basics.
> Because the application uses around 250 tables in mysql and I would
> like to make it righ t the first time
>
> when migrating to pgsql..
I would suggest that you start off with a 'smaller' project and explore
all the ways where postgresql does things differently to and/or better
than mysql, then work your way up to a more complex project. This would
be much better than doing a hasty migration to postgresql - which does
not make the most of what postgresql has to offer - and then trying to
hack the postgresql features in afterwards.
--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
------------------------------------------
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
------------------------------------------
New Year Resolution: Ignore top posted posts
[Back to original message]
|