Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iKlWM-0004c0-9B for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2019 15:47:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iKlWJ-0000kC-Su for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2019 15:47:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iKlWJ-0000jv-F5 for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2019 15:47:27 +0000 Received: from sonic308-2.consmr.mail.bf2.yahoo.com ([74.6.130.41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iKlWG-00026j-2A for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2019 15:47:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1571240840; bh=LdWWFZmJPs7VXAHlci8mJkeb8EPe+eVUa2CrhPCYyRI=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=OnpKQonqwKsx6sKkjxm5ECQEGhXW29f++mWrF8sC5Y8Y9Y7MAGj+LljjBXklWo6MJOr3qlwer2vLWjyg2ftnwolc2hgH42rFBUjNndiDTUTVdy+aboY4XpAnREJg/21zV16nW3xb7e/LjTfqk9gyo6HAmwyO/KqURV9v7OJoIaKEZzlMFyhmdTQTZ/3KhC7Vt8Oa4mxusIQB61T+yhwsbB0Em0WRF4oS1geqBMk18P/gcKyylvM6kFEDW3zsFTCBbM3xEFSBRbUJ7SEZtUnQkcnhmbXqDu04iTCbweGo5EpQk2up9pQHCo1yEA3G0g/tXgmTJWuvLF0iLrWGI6BtFg== X-YMail-OSG: lKfCCX0VM1kEAHlxL4w0p2c4r.aZXiPdgtTE0GlNBkLo2H9jox6_FXpsHazZIgc xchhXqMkX__tWtn7GFg3fmkgjK_kj1yEr1vQ5BRURNG7FYemCnRbZKedob_RHil.YUYwh3U7L12y mggoj89u28uc4dyUSA_pP9xPVorO_F26erka.hrkKfQlHqSIUGsOTOQ9UzGfPKTYQbPMU1bB42Pw g0hZczr0CZA_EbVJuZ259T87jpb6ZU_4ia7QDqPEYhVTxpa3S60w8ucpepIbkvR9hYzK1KD04NZn UoE75ZTRTapzDYc06tZS_Yz801rxN7Rz18HgL.ykxvnwwYul_ab8QHdD5.1J2ziKttqcYLKhPdih B7pQG6MxsjpaekC_afGsN0Ss1ng_yYAdgvttrAnChf0vTzIF7OfdwAdziSh9xxCv0vqbMDhUPrQN DCLJP1BbAWBd7bhJTV7NmfuMl1H1l44qcS8bppXmoofcAyH_C.t9LICYvmfkCCucxwNGFifR4QdE dFmQDDi1l12mweD2LDuSYv4FKbqoEpsy6OWGPpE_Ch2G4voosJbY71c9O5U18ZUWfDzW9WLnimDI DxQIvqYAMJOJckXIJOfIMndI_PMJheBfYHv5YNljo5lgNCz.u4v_2TCGX3QTGPC9YXbytHZcRvIb p44Gd4i7sn7Le7LDeLakBhopvVnIGzR4x3E.IJlx1rYd3.CzIhUah_3YTbGgXClE89c9RSsArUCh 7Zef5rs6FwNecUkoCWbBZwfTa.v5Yin254CcGu70W.PcreNoz4zkDh8K5UVM4aTaZRffD.Kan1WW __jIZa9TpUJPmL_RtGtt2VAnhOZtTQgLQXcfhYpVcFzepADn96QA_4Op.RrN_hlvDg9s7fSdlcbx c.OqiyEzdQKP.h93xMRfvF5jvMLz_8dJ0mo6pZQ1tKIyXmdpW0kjznXmyGpZx91zDSY2ew4idyB5 elFOY8A1SjEf7SVYidxvhlR0CFBIgSElYVE6XvIqyhP3neczOMgJ0gJrWukKBGqjItNoVp1NFToK L7OSpWCtp8U9NSjI7muRWg6KcSp9qa6RUGtdpEZOZVqS9gCyu8mDD6A66Sc4Be9MeZsMnBursv4s T_Dy3UUGVWhONJEeAY.a.NLHHBYUFJ1jvDvGd5c3leMPzGLe2bXUHVZCcMqFcZ.YqBrd3sJcahhC t6m_FclLyQ0JkBNf2MqOBieN3NI7sav1q0UDLglvQ2TY297U25gOX0Mfst.JcHO86n2gzo_JmNgJ Lo_8BTcjx5ms4ljMYqFzLNnEQgi1SLD384Q2mUT5cfiZG9wNgw8nX36vywgNfFvYkbbWq1u.w1SI 5VaTC6cUUmI.K_ZbCJns6H2To0.Gx6ysPRSBnb0wVZPIOFWaJQUD8u9wVI4yPtLhFIbgW.Yt_9E. 1 Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.bf2.yahoo.com with HTTP; Wed, 16 Oct 2019 15:47:20 +0000 Date: Wed, 16 Oct 2019 15:47:12 +0000 (UTC) From: Karen Goh To: Steve Midgley Cc: Message-ID: <1747806136.1620685.1571240832461@mail.yahoo.com> In-Reply-To: References: <1846839195.1533405.1571225796919@mail.yahoo.com> Subject: Re: Should I add a Index Key in this case ? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1620684_1681191569.1571240832459" X-Mailer: WebService/1.1.14498 YahooMailIosMobile Yahoo%20Mail/47456 CFNetwork/978.0.7 Darwin/18.6.0 Content-Length: 6488 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_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 wrote: On Wed, Oct 16, 2019 at 7:36 AM Karen Goh 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 Hi Steve,

My question is should I use index on the Seat_= Viewing_id ?

I have no experience in using Index h= ence I asked if it should be made auto-incremental?

Kindly advise how should I alter my existing table to have index. 

Tks!


Sent from Yahoo Mail for iPhone

On Wednesday, October 16, 2019, 11:26 PM, Steve Midgl= ey <science@misuse.org> wrote:



On Wed, Oct 16, 2019 at 7:36 AM K= aren Goh <karenwo= rld@yahoo.com> wrote:
Hi Experts,

I have a use case as follows :

 add 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:


    SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCRE= MENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT A_pkey PRIMARY KEY (SEAT_id))
WITH (
    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 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: https://www.postgresql.org/d= ocs/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--