Roooooooooooooll back!

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.

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"

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

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?

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

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

Hey,

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"
If not, what would be the syntax to perform such a query?

That should work be fine. (Pretty standard SQL I think).

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?

The same way as with mysql, its standard SQL.
SELECT foo1, foo2 from bar where id = 'number';

It appears from
examples that I would do:
"INSERT INTO people ('people_id_seq', 'name') VALUES
(nextval('people_id_seq', 'name');"

No, it would still be INSERT INTO people ('people_id','name') VALUES
(nextval('people_id_seq'),'name');

You don't have to say the full sequence name, just the column name you wish
to access. But.. you don't explicitly need the nextval(..), there are
advantages to using it, such as if you need to insert this value into another
table somewhere else. You cans till write the query as

INSERT INTO people(name) VALUES ('name');

& postgresql will automatically add one to the sequence, and update that info
in the table.

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, you don't have to.

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

Hope it makes some sense :)

--

Chris Smith
http://www.squiz.net

On Mon, 14 May 2001, GH wrote:

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.

I was under the impression id was a serial and therefore an int. You
don't need to quote and int:

asdf=# select person_name, person_age from names, ages where
names.personid=3 and names.personid=ages.personid;
person_name | person_age
-------------+------------
ralph | 12
(1 row)

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================