X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EAD4952940 for ; Wed, 20 Jul 2005 19:04:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76345-02 for ; Wed, 20 Jul 2005 22:04:10 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.204]) by svr1.postgresql.org (Postfix) with ESMTP id ED7E6528BD for ; Wed, 20 Jul 2005 19:04:08 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id g2so409852nfe for ; Wed, 20 Jul 2005 15:04:10 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nEqPguq9hyep3ISnEUVqsEsMptLc59zwJhPFEJ5yyIf9jOZ4Sn0Oj7IUvMUjGo4HMj2fZeQjYb51IF8JmvK9DY3Pt2JQ9B4WIhGDMaqEhzrXV3pMFq9qMgTJ5VEpBEbUTfdmbUjiGWIltRaJxx8AQPrSot7WpC7QP8HjiWZkp1Q= Received: by 10.48.250.11 with SMTP id x11mr34562nfh; Wed, 20 Jul 2005 15:04:10 -0700 (PDT) Received: by 10.48.144.16 with HTTP; Wed, 20 Jul 2005 15:04:10 -0700 (PDT) Message-ID: Date: Wed, 20 Jul 2005 23:04:10 +0100 From: Guido Barosio Reply-To: Guido Barosio To: "Goulet, Dick" Subject: Re: Add new column Cc: Rafal Kedziorski , pgsql-admin@postgresql.org In-Reply-To: <4001DEAF7DF9BD498B58B45051FBEA6502A529ED@25exch1.vicorpower.vicr.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9247_14379335.1121897050903" References: <4001DEAF7DF9BD498B58B45051FBEA6502A529ED@25exch1.vicorpower.vicr.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.246 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/168 X-Sequence-Number: 18224 ------=_Part_9247_14379335.1121897050903 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Once ago I had a similar problem.=20 The developers team requested about creating a new column, and the placemen= t was important. Thus, changing the app code was too expensive. A bad habit,= =20 was my reply,=20 but as expected I had to found a solution for the problem. Create a view and rename the table, it will add a overhead, but it sort=20 my problem.=20 (...and they didn't notice ;) ) Regards, Guido On 7/20/05, Goulet, Dick wrote:=20 >=20 > Then drop and rebuild the table. Otherwise add it onto the end, which I > believe is ANSI SQL expected behavior and forget about it. Where the > column is in the table is less important than where you place it in the > select statement. The only other time I can see column placement as > being important is if it's part of the primary key, but even then I've > had no problems with adding to the end of the table. >=20 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rafal Kedziorski > Sent: Wednesday, July 20, 2005 4:49 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Add new column >=20 > Hi, >=20 > I wan't add new column to this table: >=20 > CREATE SEQUENCE abo_product_2_user_seq; >=20 > CREATE TABLE abo_product_2_user ( > abo_product_2_user_id BIGINT NOT NULL DEFAULT > NEXTVAL('abo_product_2_user_seq'), > abo_product_id SMALLINT NOT NULL, > user_id BIGINT NOT NULL, > valid_from TIMESTAMP NOT NULL, > valid_to TIMESTAMP, > debit_entry VARCHAR(12) NOT NULL, > -- > PRIMARY KEY (abo_product_2_user_id), > --INDEX (abo_product_id), > --INDEX (user_id), > FOREIGN KEY (abo_product_id) > REFERENCES abo_product (abo_product_id), > FOREIGN KEY (user_id) > REFERENCES users (user_id) > ); >=20 > But PostgreSQL 8.0.3 adds the new column at the end. I want add this > new column between user_id and valid_from. >=20 >=20 > Best Regards, > Rafal >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? >=20 > http://archives.postgresql.org >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >=20 --=20 "Adopting the position that you are smarter than an automatic optimization algorithm is generally a good way to achieve less performance, not more" - Tom Lane. ------=_Part_9247_14379335.1121897050903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Once ago I had a similar problem.
The developers team requested about creating a new column, and the pla= cement
was important. Thus, changing the app code was too expensive. A bad ha= bit, was my reply,
but as expected I had to found a solution for the problem.
 
Create a view and rename the table, it will add a overhead, but it sor= t
my problem.
 
(...and they didn't notice ;) )
 
Regards,
Guido

 
On 7/20/05, = Goulet, Dick <DGoulet@vicr.com> wrote:
Then drop and rebuild the table.=   Otherwise add it onto the end, which I
believe is ANSI SQL e= xpected behavior and forget about it.  Where the
column is in the table is less important than where you place it in the=
select statement.  The only other time I can see column place= ment as
being important is if it's part of the primary key, but even the= n I've
had no problems with adding to the end of the table.

-----Origin= al Message-----
From:
pgsql-admin-owner@postgresql.org
[mailto: pgsql-admin-owner@postgresql.org] On Behalf Of Rafal Kedziorski
Sent= : Wednesday, July 20, 2005 4:49 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Add new col= umn

Hi,

I wan't add new column to this table:

CREATE SEQU= ENCE abo_product_2_user_seq;

CREATE TABLE abo_product_2_user (
&n= bsp; abo_product_2_user_id  BIGINT    &n= bsp; NOT NULL DEFAULT
NEXTVAL('abo_product_2_user_seq'),
  abo_product_id       &nb= sp; SMALLINT    NOT NULL,
  user_id &= nbsp;           &nbs= p;  BIGINT      NOT NULL,
 =  valid_from          = ;   TIMESTAMP   NOT NULL,
  valid_to =             &nb= sp; TIMESTAMP,
  debit_entry     &nbs= p;      VARCHAR(12) NOT NULL,
  --
  PRIMARY KEY (abo_product_2_user_id),
&= nbsp; --INDEX (abo_product_id),
  --INDEX (user_id),
&= nbsp; FOREIGN KEY (abo_product_id)
     &n= bsp;  REFERENCES abo_product (abo_product_id),
  FOR= EIGN KEY (user_id)
        REFERENCES users (user_id)<= br>);

But PostgreSQL 8.0.3 adds the new column at the end. I want ad= d this
new column between user_id and valid_from.


Best Regard= s,
Rafal


---------------------------(end of broadcast)-------= --------------------
TIP 4: Have you searched our list archives?

   &n= bsp;          http://archives.postgresql.org

-= --------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to      choose an index scan if your joining c= olumn's datatypes do not
      match



--
"Adopting the position that you ar= e smarter than an automatic
optimization algorithm is generally a good way to achieve less
perfo= rmance, not more" - Tom Lane.=20 ------=_Part_9247_14379335.1121897050903--