Re: Should I add a Index Key in this case ?
Karen Goh <[email protected]> Wed, 16 Oct 2019 15:47:12 +0000 (UTC)
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_1620684_1681191569.1571240832459 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Steve, My question is should I use index on the Seat_Viewing_id ? I have no experience in using Index hence I asked if it should be made auto= -incremental? Kindly advise how should I alter my existing table to have index.=C2=A0 Tks! Sent from Yahoo Mail for iPhone On Wednesday, October 16, 2019, 11:26 PM, Steve Midgley <[email protected]= > wrote: On Wed, Oct 16, 2019 at 7:36 AM Karen Goh <[email protected]> wrote: Hi Experts, I have a use case as follows :=20 =C2=A0add constraints to the database so that no two reservations for the s= ame viewing may refer to the same seat. So, say I have a primary key like this in the table A: =C2=A0 =C2=A0 SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCRE= MENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ), =C2=A0 =C2=A0 CONSTRAINT A_pkey PRIMARY KEY (SEAT_id)) WITH ( =C2=A0 =C2=A0 OIDS =3D FALSE ) TABLESPACE pg_default; So, basically I would like to create a SEAT_Viewing_Id. In this case, do I create a Index Key or ? How should I construct or rather alter my table A to accomodate this SEAT_V= iewing_Id ? Hope someone can tell me how. Furthermore, whenever an insertion is done via WebApp, I would have to inse= rt the Index key as well or does PostgreSQL will have a way to increment th= e Index key which is the SEAT_Viewing_Id at the same time? Thanks & regards, Karen I'm not sure I understand exactly what you want to do, but it sounds like y= ou are want to create a second field/column in your table named "SEAT_viewi= ng_id" and you want that field to auto-increment independently from the pri= mary key? If so, you can consider the "serial" datatype as possiblye meetin= g your needs: https://www.postgresql.org/docs/current/datatype-numeric.html= #DATATYPE-SERIAL So just `alter table`, to add your new field, and make its datatype `serial= `. Apologies if I misunderstood your question,Steve ------=_Part_1620684_1681191569.1571240832459 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <html xmlns=3D"http://www.w3.org/1999/xhtml" xmlns:v=3D"urn:schemas-microso= ft-com:vml" xmlns:o=3D"urn:schemas-microsoft-com:office:office"><head><!--[= if gte mso 9]><xml><o:OfficeDocumentSettings><o:AllowPNG/><o:PixelsPerInch>= 96</o:PixelsPerInch></o:OfficeDocumentSettings></xml><![endif]--></head><bo= dy> Hi Steve,<div><br></div><div>My question is should I use index on the Seat_= Viewing_id ?</div><div><br></div><div>I have no experience in using Index h= ence I asked if it should be made auto-incremental?</div><div><br></div><di= v>Kindly advise how should I alter my existing table to have index. </= div><div><br></div><div>Tks!<br><br><br><a href=3D"https://overview.mail.ya= hoo.com/?.src=3DiOS">Sent from Yahoo Mail for iPhone</a><br><br><p class=3D= "yahoo-quoted-begin" style=3D"font-size: 15px; color: #715FFA; padding-top:= 15px; margin-top: 0">On Wednesday, October 16, 2019, 11:26 PM, Steve Midgl= ey <[email protected]> wrote:</p><blockquote class=3D"iosymail"><div= id=3D"yiv7815175159"><div><div dir=3D"ltr"><div dir=3D"ltr"><br clear=3D"n= one"></div><br clear=3D"none"><div class=3D"yiv7815175159gmail_quote"><div = class=3D"yiv7815175159yqt3649804408" id=3D"yiv7815175159yqt84110"><div clas= s=3D"yiv7815175159gmail_attr" dir=3D"ltr">On Wed, Oct 16, 2019 at 7:36 AM K= aren Goh <<a rel=3D"nofollow" shape=3D"rect" ymailto=3D"mailto:karenworl= [email protected]" target=3D"_blank" href=3D"mailto:[email protected]">karenwo= [email protected]</a>> wrote:<br clear=3D"none"></div><blockquote class=3D"y= iv7815175159gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px = solid rgb(204,204,204);padding-left:1ex;">Hi Experts,<br clear=3D"none"> <br clear=3D"none"> I have a use case as follows : <br clear=3D"none"> <br clear=3D"none"> add constraints to the database so that no two reservations for the s= ame viewing may refer to the same seat.<br clear=3D"none"> <br clear=3D"none"> So, say I have a primary key like this in the table A:<br clear=3D"none"> <br clear=3D"none"> <br clear=3D"none"> SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCRE= MENT 1 START 1 MINVALUE 1 MAXVALUE <a dir=3D"ltr" href=3D"tel:2147483647" x= -apple-data-detectors=3D"true" x-apple-data-detectors-type=3D"telephone" x-= apple-data-detectors-result=3D"2">2147483647</a> CACHE 1 ),<br clear=3D"non= e"> CONSTRAINT A_pkey PRIMARY KEY (SEAT_id))<br clear=3D"none"> WITH (<br clear=3D"none"> OIDS =3D FALSE<br clear=3D"none"> )<br clear=3D"none"> TABLESPACE pg_default;<br clear=3D"none"> <br clear=3D"none"> So, basically I would like to create a SEAT_Viewing_Id.<br clear=3D"none"> <br clear=3D"none"> In this case, do I create a Index Key or ?<br clear=3D"none"> <br clear=3D"none"> How should I construct or rather alter my table A to accomodate this SEAT_V= iewing_Id ?<br clear=3D"none"> <br clear=3D"none"> Hope someone can tell me how.<br clear=3D"none"> <br clear=3D"none"> Furthermore, whenever an insertion is done via WebApp, I would have to inse= rt the Index key as well or does PostgreSQL will have a way to increment th= e Index key which is the SEAT_Viewing_Id at the same time?<br clear=3D"none= "> <br clear=3D"none"> Thanks & regards,<br clear=3D"none"> Karen<br clear=3D"none"> <br clear=3D"none"> <br clear=3D"none"></blockquote></div><div>I'm not sure I understand exactl= y what you want to do, but it sounds like you are want to create a second f= ield/column in your table named "SEAT_viewing_id" and you want that field t= o auto-increment independently from the primary key? If so, you can conside= r the "serial" datatype as possiblye meeting your needs: <a rel=3D"nofollow= " shape=3D"rect" target=3D"_blank" href=3D"https://www.postgresql.org/docs/= current/datatype-numeric.html#DATATYPE-SERIAL">https://www.postgresql.org/d= ocs/current/datatype-numeric.html#DATATYPE-SERIAL</a></div><div><br clear= =3D"none"></div><div>So just `alter table`, to add your new field, and make= its datatype `serial`.<br clear=3D"none"></div><div><br clear=3D"none"></d= iv><div>Apologies if I misunderstood your question,</div><div>Steve</div><d= iv><br clear=3D"none"></div></div></div></div></div><blockquote></blockquot= e></blockquote></div> </body></html> ------=_Part_1620684_1681191569.1571240832459--