Обсуждение: Inserting a new column in between.

I want to know whether I can insert a new column in between in an existing
table. My table has 10 fields and I want to insert a field after third
field.
--
View this message in context: http://www.nabble.com/Inserting-a-new-column-in-between.-tf3286148.html#a9140871
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

RPK wrote:
> I want to know whether I can insert a new column in between in an existing
> table. My table has 10 fields and I want to insert a field after third
> field.

Not without recreating the table.

Sincerely,

Joshua D. Drake

--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

I think pgsql front-end needs this facility in future.


Joshua D. Drake wrote:
>
> RPK wrote:
>> I want to know whether I can insert a new column in between in an
>> existing
>> table. My table has 10 fields and I want to insert a field after third
>> field.
>
> Not without recreating the table.
>
> Sincerely,
>
> Joshua D. Drake
>
> --
>
>       === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive  PostgreSQL solutions since 1997
>              http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

--
View this message in context: http://www.nabble.com/Inserting-a-new-column-in-between.-tf3286148.html#a9141431
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

am  Sat, dem 24.02.2007, um 23:08:05 -0800 mailte RPK folgendes:
>
> I think pgsql front-end needs this facility in future.

No. You can specify the column-order in the SELECT or INSERT - command.
And you can do it with regular SQL. PostgreSQL possesses transactions.
>
>
> Joshua D. Drake wrote:

Please, no useless top posting with fullquote below - its hard to read
and understand, and a waste of bandwith and memory.

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Andreas,

I am talking about inserting a field or changing their order in the
structure itself. In MS Access and SQL Server we have this facility. Some
times I need to shift the less important field to the last so that when I
query using:

Select * from tablename;

the un-necessary field gets displayed in the last. However, you I can ignore
that field altogether using filtered fields only in Select statement, but
still sometimes it is necessary.

--
View this message in context: http://www.nabble.com/Inserting-a-new-column-in-between.-tf3286148.html#a9141803
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

RPK wrote on 25.02.2007 09:44:
> Select * from tablename;
>
> the un-necessary field gets displayed in the last. However, you I can ignore
> that field altogether using filtered fields only in Select statement, but
> still sometimes it is necessary.
>
No it's not. SELECT * should be avoided by all means (except for ad-hoc queries).

Thomas

RPK <rohitprakash123@indiatimes.com> schrieb:

>
> Andreas,
>
> I am talking about inserting a field or changing their order in the
> structure itself. In MS Access and SQL Server we have this facility. Some
> times I need to shift the less important field to the last so that when I
> query using:
>
> Select * from tablename;

You should don't do that! Why? For instance, you have a table with many
rows, including BLOBs. Your applivation needs only a few columns, but
you select * returns the whole rows.


So, but no problem:

test=# create table foo (a int, c int);
CREATE TABLE
test=*# insert into foo values (1,3);
INSERT 0 1
test=*# commit;
COMMIT


Okay, we have a table with columns a and c, and now i notice i forgot
the column b. No problem:

test=# begin;
BEGIN
test=*# create table foo_temp as select a, null::int as b, c from foo;
SELECT
test=*# drop table foo;
DROP TABLE
test=*# alter table foo_temp rename to foo;
ALTER TABLE
test=*# commit;
COMMIT
test=# select * from foo;
 a | b | c
---+---+---
 1 |   | 3
(1 row)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Hi RPK

My suggestion would be to create a view which presents the columns as
you wish.

eg

CREATE OR REPLACE VIEW tableView AS
 SELECT a, b, c
   FROM table

Then when you add add a new column, just adjust the view as required.

Cheers
Noel

RPK wrote:
> Andreas,
>
> I am talking about inserting a field or changing their order in the
> structure itself. In MS Access and SQL Server we have this facility. Some
> times I need to shift the less important field to the last so that when I
> query using:
>
Postgres does not allow such ordering as stated in the previous replies.