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.