Re: PostgreSQL in Comparison to mySQL

Hi Jason,

How're you going with this?

The standard JOIN syntax is normal SQL. i.e. select a, b from c,d where
c.foo = d.foo and f.bar = 'something'.

etc.

The "serial" type is really an INTEGER column. When you define it
(create table baz (frog serial, color varchar(20)) then it's really
doing a few things :

a) creating the table (i.e. create table baz (frog integer, color
varchar(20))

b) creating a "sequence", kind of like a one record table. The name of
the sequence if based on the name of the table and column that it's made
for. i.e "baz_frog_seq" would be the name in this case. The sequence
table-like-structure has a few columns of it's own, namely a minimum
value ("min_value") which it will start at and wrap around to (when it
wraps around); a maximum value ("max_value") it will get to before
wrapping around, a counter to keep track of where it's up to
("last_value") and a few other bits. These are the main one's you'll be
interested in at first.

c) creates an index on the serial column, also based on the name of the
table and column ("baz_frog_key");

d) create a default value for the column that's the serial one. The
default value isn't a number, it's actually a trigger to get the next
number from it's associated sequence.

When you insert values into a table with a sequence, you just leave out
the sequence column, and the default kicks in to fill it in with the
next available value from the sequence.

insert into baz (color) values ('Fluro Green');

select * from baz;

frog | color
------+-------------
1 | Fluro Green

insert into baz (color) values ('Stripy Orange');

select * from baz;

frog | color
------+-------------
1 | Fluro Green
2 | Stripy Orange

See how it goes?

There are also a few ways of changing the values the sequence uses, the
easiest being to use these three functions :

select currval('<sequence name>');

Gets the value it's up to.

select setval('<sequence name>', <new value>);

Gives the sequence a new value to continue on from.

select nextval('<sequence name>');

Retrieves the next value from the sequence, and increments it's
counters. This is actually the same thing that gets called when you do
an insert into a table and don't give a value to the sequence, making it
fill in its own value.

One last point, you can insert values directly into a table's sequence
column, in case you don't want it to look up it's own next value.

insert into baz (55, 'Transparent'); (OR you could use insert into baz
(frog, color) values (55, 'Transparent'))

select * from baz;

frog | color
------+-------------
1 | Fluro Green
2 | Stripy Orange
55 | Transparent

Be careful in this situation for when the sequence catches up to the 55
value as it would in the example above!

Play with them a bit, there are even a few other nifty things you can do
with them, but you'll start figuring out exactly what when you need to
or are starting to get the hang of it.

Here's a starter for when you are getting the hang of it, try tying a
few tables to the same sequence, as in two transaction log tables having
unique transaction numbers, and they're not allowed to share transaction
numbers. Well, you'll see the point then anyway. :-)

Hope that's useful.

Regards and best wishes,

Justin Clift

> Jason wrote:
>
> 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.