Thread: Add new column

Add new column

From
Rafal Kedziorski
Date:
Hi,

I wan't add new column to this table:

CREATE SEQUENCE abo_product_2_user_seq;

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)
);

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


Best Regards,
Rafal


Re: Add new column

From
Dawid Kuroczko
Date:
On 7/20/05, Rafal Kedziorski <rafcio@polonium.de> wrote:
> Hi,
>
> I wan't add new column to this table:
>
> CREATE SEQUENCE abo_product_2_user_seq;
>
> 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)
> );
>
> But PostgreSQL 8.0.3 adds the new column at the end. I want add this
> new column between user_id and valid_from.

Column reordering is not supported.  If you really need an exact order
you have three choices:

1. Use explicit column names in SELECTs (not SELECT *).

2. CREATE VIEW with column order you desire.

3. Create a new table and populate it with data from old table, then drop
old table and rename the new one.

   Regards,
     Dawid

Re: Add new column

From
"Goulet, Dick"
Date:
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.

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

Hi,

I wan't add new column to this table:

CREATE SEQUENCE abo_product_2_user_seq;

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)
);

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


Best Regards,
Rafal


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

               http://archives.postgresql.org

Re: Add new column

From
Guido Barosio
Date:
Once ago I had a similar problem.
The developers team requested about creating a new column, and the placement
was important. Thus, changing the app code was too expensive. A bad habit, 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 sort
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 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.

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

Hi,

I wan't add new column to this table:

CREATE SEQUENCE abo_product_2_user_seq;

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)
);

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


Best Regards,
Rafal


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

              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 column's datatypes do not
      match



--
"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.