Re: PostgreSQL in Comparison to mySQL

Quoting Jason <jason(at)op480(dot)com>:

> Hi,
>
> I'm your typical mySQL user who hasn't used PostgreSQL much at all compared
> to the former. I'm getting ready to port my current site in mySQL to
> PostgreSQL mainly to harness the power of transactions and triggers. I
> wanted to see if someone could help clarify a few things between the two.

I have to pass a database we have on an old MySQL to PostgreSQL soon. :-)

> The site does not use many complex queries. It's just basic updates, basic
> inserts, and some basic selects and joined selects.
> My questions revolve mostly among joined selects and auto_increment (serial
> in pg) syntaxes.
>
> Can joined selects in pg be accomplished the same way as in mySQL?
> ie- "select person_name, person_age from names, ages where names.id=4 and
> names.id=ages.person_id"

Ofcourse, but it would be good to use PRIMARY KEYs and FORIEGN KEYs to tie the 2
columns. I heard MySQL doesn't have this feature.

> If not, what would be the syntax to perform such a query?
>
> Also, I'm still a little unclear on how one utilizez the serial feature:
> In examples it seems like a serial type is not actually a column, but a
> sequence with a special name. I'm going to assume the following:
> Say I create a serial column called id on a table named people... how would
> I reference that in selects, updates, inserts, etc? It appears from
> examples
> that I would do:
> "INSERT INTO people ('people_id_seq', 'name') VALUES
> (nextval('people_id_seq', 'name');"

SERIAL is an INT column with a sequence. The sequence watches whats going on
with the column. nextval() and currval are functions that take the current value
and next value og the sequence.
If you define the column as SERIAL you don't have to do that on inserts, because
the column will have a default value of nextval('sequence_name').

> In mySQL you don't have to explicitly define the vaule for the
> auto_increment column, it will automatically select the next value upon
> insert.
> However, from what I gathered you DO have to explicitly define the nextval
> for a serial column type. Is this true? If so, does the query above look
> accurate?

No!

Read the INT data type, and the SERIAL data type.

Saludos... :-)

--
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------