Posted by "Richard Lynch" on 06/30/05 23:06

On Thu, June 30, 2005 11:55 am, Uro¹ Kristan said:
> I have an application in production, build on mysql database.
>
> I decided to migrate to postgres because of numerous reasons.
>
> Can you guys please guide me into the right direction?
>
> the main problem is the missing autoincrement of pgsql and getting the

I always do this:

create sequence TABLENAME_id;
create TABLENAME (
TABLENAME_id int4 unsigned default(nextval('TABLENAME_id'))
)
create unique index TABLENAME_id_index on TABLENAME(TABLENAME_id);

You can use "unique not null primary key" in the column definition, but
then the name of the index is something I can't remember.

> last
> record from the tabel, for linking to another tabel.

You have to use http://php.net/pg_last_oid to get the PostgreSQL
"internal" Object ID (OID) -- You can then use the ubiquitous "oid"
column.

$query = "insert ...";
pg_exec($connection, $query);
$oid = pg_last_oid($connection);
$query = "select TABLENAME_id from TABLENAME where oid = $oid";
$id = pg_exec($connection, $query);
$id = pg_result($id, 0, 0);

Of course, the above code has no error-checking or anything like that, so
it gets about twice as long as that in Real Life.

It is possible to configure PostgreSQL to *not* have the oid stored in
each record, if you are really really really cramped for disk space, but
you have to *KNOW* in advance that you won't need to use the OID as above,
which is pretty rare... I daresay that you'd have to be in a REALLY
high-performance and high-tolerance for error application to be able to
get away with that.

If somebody made such an almost-for-sure unwise decision to not have OID
fields in the tables, you are SOL.

> How do you deal with that?
> also, can you please recommend me some good manual, explanation or book to
> help me with this problem.
>
> 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 was thinking about using the pear db wrapper class, but
>
>
>
> Regards,
>
> Uro¹ KRISTAN
>
>


--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]