Thread: Roooooooooooooll back!

Quoting Jason <jason@op480.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@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

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