Re: PostgreSQL in Comparison to mySQL - Mailing list pgsql-general
| From | HYip |
|---|---|
| Subject | Re: PostgreSQL in Comparison to mySQL |
| Date | |
| Msg-id | 3B0ACC39.2AA0A0B5@earthlink.net Whole thread Raw |
| In response to | PostgreSQL in Comparison to mySQL ("Jason" <jason@op480.com>) |
| List | pgsql-general |
You can use the join statement as mySQL.
You don't need to specify the serial with nextval. The server will insert the
nextval by default.
Jason wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0008_01C0DC89.B4988AA0
> Content-Type: text/plain; charset="iso-2022-jp"
> Content-Transfer-Encoding: 7bit
>
> 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.
>
> ------=_NextPart_000_0008_01C0DC89.B4988AA0
> Content-Type: text/html; charset="iso-2022-jp"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dwindows-125=
> 2">
> <META content=3D"MSHTML 5.50.4522.1800" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2>Hi,</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>I'm your=20
> typical mySQL user who hasn't used PostgreSQL much at all compared to the=
> =20
> former. I'm getting ready to port my current site in mySQL to PostgreSQL ma=
> inly=20
> to harness the power of transactions and triggers. I wanted to see if someo=
> ne=20
> could help clarify a few things between the two.</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>The site=20
> does not use many complex queries. It's just basic updates, basic inserts, =
> and=20
> some basic selects and joined selects.</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>My=20
> questions revolve mostly among joined selects and auto_increment (serial in=
> pg)=20
> syntaxes.</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>Can joined=20
> selects in pg be accomplished the same way as in mySQL?</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>ie- "select=20
> person_name, person_age from names, ages where names.id=3D4 and=20
> names.id=3Dages.person_id"</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>If not,=20
> what would be the syntax to perform such a query?</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>Also, I'm=20
> still a little unclear on how one utilizez the serial=20
> feature:</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>In examples=20
> it seems like a serial type is not actually a column, but a sequence with a=
> =20
> special name. I'm going to assume the following:</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>Say I=20
> create a serial column called id on a table named people... how would I=20
> reference that in selects, updates, inserts, etc? It appears from examples =
> that=20
> I would do:</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2> "INSERT INTO people ('people_id_seq', 'name') VALUES=20
> (nextval('people_id_seq', 'name');"</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>In mySQL=20
> you don't have to explicitly define the vaule for the auto_increment column=
> , it=20
> will automatically select the next value upon insert.</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>However,=20
> from what I gathered you DO have to explicitly define the nextval for a ser=
> ial=20
> column type. Is this true? If so, does the query above look=20
> accurate?</FONT></SPAN></DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV dir=3Dltr><SPAN class=3D100125220-14052001><FONT face=3DTahoma size=3D=
> 2>Thanks for=20
> the info to help me make the migration to a real=20
> RDBMS.</FONT></SPAN></DIV></BODY></HTML>
>
> ------=_NextPart_000_0008_01C0DC89.B4988AA0--
pgsql-general by date: