Re: PostgreSQL in Comparison to mySQL

On Mon, May 14, 2001 at 02:07:03PM -0700, some SMTP stream spewed forth:
> Hi,
>
*snip*
> 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"

Yes.
You can even do:
select person_name, person_age from names n, ages a where n.id='4' and
n.id=a.person_id

BUT, you must single-quote attribute values, e.g. id, etc.

> If not, what would be the syntax to perform such a query?

You can also use some of the more advanced outer, inner joins, union
selects, etc.

> 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');"

The serial datatype is simply a shortcut psuedotype.
A serial column is translated to an int with the default value being a
value pulled from a created sequence.
create table blah (some_col serial)
is functionally equal to
create sequence some_col_seq;
create table blah (some_col int default(nextval('some_col_seq')::int));

(IIRC, the int cast is gratuitous.)

> 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?

SOP (standard operating practice) is to select nextval(sequence) first and
use that value in an insert, but you can simply
insert into blah (columns_other_than_the_serial) values('whatever') and
the sequence value will be inserted. (This is true for any type of
`default' setup.

> Thanks for the info to help me make the migration to a real RDBMS.

Hope this helps.
dan