am Tue, dem 03.10.2006, um 16:17:30 +0530 mailte Penchalaiah P. folgendes:
now I want to add one more field in this table but that field has to come
next to cda_no I mean as a 3^rd field If I am adding that field it is
coming last field ?

Yes.

may I know how it is possible to that table?

IIRC you can't. But you can:
- select the columns in the order you need (never do a select * from
)
- create a view with your desired order
- create a new table with your desired order

Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and/ or its Customers and is intended for use only by the individual

Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the individual

Is this realy necessary?

Andreas

03 2006 13:47, / Penchalaiah P. *:
Hi

I have one table with 12 fields
>
>
>
CREATE TABLE addition_alteration_memo

(

addition_alteration_memo int8 NT NULL DEFAULT
nextval(''::regclas
s),

cda_no varchar(7) NT NULL,

week numeric,

sheet_no numeric,

serial_no numeric,

date date,

dr_no varchar,

amount numeric,

memo_no varchar,

memo_date date,

no_instalments numeric,

instalment_rate numeric)
>
>
>
now I want to add one more field in this table but that field has to
come next to cda_no I mean as a 3rd field If I am adding that field
it is coming last field

may I know how it is possible to that table

2 notes:
1) Why do you feel that this presentational reordering is important. Normally
it should not be important, otherwise some engineering flaw is hanging
around.
2) If you are sure it is important, one way to do this, is dump, change the
order of the field in the .sql and restore.

--
Thanks & Regards

Penchal reddy | Software Engineer

Infinite Computer Solutions | Exciting TimesInfinite Possibilities
--
SEI-CMMI level 5 | IS 9001:2000

IT SERVICES | BP
--
Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities |
Retail & Distribution | Government
--
Tel +91-80-5193-0000(Ext:503)| Fax +91-80-51930009 | Cell No
+91-9980012376|www.infics.com

Information transmitted by this e-mail is proprietary to Infinite
Computer Solutions and/ or its Customers and is intended for use only by
the individual or entity to which it is addressed, and may contain
information that is privileged, confidential or exempt from disclosure
under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper
authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases, please
notify us immediately at info.in (AT) infics (DOT) com and delete this mail from
your records.
>
>
>
>
>
Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the
individual or the entity to which it is addressed, and may contain
information that is privileged, confidential or exempt from disclosure
under applicable law. If you are not the intended recipient or it appears
that this mail has been forwarded to you without proper authority, you are
notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at
info.in (AT) infics (DOT) com and delete this email from your records.

Hi, Penchalaiah,

Penchalaiah P. wrote:

now I want to add one more field in this table but that field has to
come next to cda_no I mean as a 3^rd field If I am adding that field
it is coming last field

In SQL, field order in the table is not given by design.

A "SELECT * FRM table" might even give you the columns alphabetically
ordered, or in a different random order each time in a different server
implementation.

If you need the colums in a specific order, use "SELECT foo, bar, baz
FRM table" or create a View.

All relevant SQL constructs (SELECT, INSERT, UPDATE, CPY, etc.) let you
specify the columns explicitly to guarantee a given order.

may I know how it is possible to that

If you _really_ want to do that despite what I wrote above, you have
several possibilities:
- CPY the table to some file, drop the table, recreate the table with
the desired new column order, and then CPY the table back using an
explicitly specified, correct row order.
- use CREATE TABLE AS SELECT to select the data into a new table,
drop the old table, rename the new one to the old one.

In both cases, you've to recreate all missing indices, foreing key
constraints etc.

HTH,
Markus

There is one non-SQL related reason that I like to be able to order
columns, at least the way they are displayed whenever the table is
described: human comprehension. For example, I like to group all keys
in a table before data, that includes primary as well as foreign keys.
So, say I'm building on to an existing application and I need to do an
ALTER TABLE on an existing table to add a foreign key to an existing
table. I'd like that key to be listed with the other keys, but
presently that's not possible in a simple way and, to be honest, I
usually just go without as the process you've described below is too
prone to user (human) error when dealing with live, sensitive data for
me to want to mess with it.

Markus Schaber wrote:
Hi, Penchalaiah,

Penchalaiah P. wrote:


>now I want to add one more field in this table but that field has to
>come next to cda_no I mean as a 3^rd field If I am adding that field
>it is coming last field
>
>
In SQL, field order in the table is not given by design.

A "SELECT * FRM table" might even give you the columns alphabetically
ordered, or in a different random order each time in a different server
implementation.

If you need the colums in a specific order, use "SELECT foo, bar, baz
FRM table" or create a View.

All relevant SQL constructs (SELECT, INSERT, UPDATE, CPY, etc.) let you
specify the columns explicitly to guarantee a given order.


>may I know how it is possible to that
>
>
If you _really_ want to do that despite what I wrote above, you have
several possibilities:

- CPY the table to some file, drop the table, recreate the table with
the desired new column order, and then CPY the table back using an
explicitly specified, correct row order.

- use CREATE TABLE AS SELECT to select the data into a new table,
drop the old table, rename the new one to the old one.

In both cases, you've to recreate all missing indices, foreing key
constraints etc.
--
HTH,
Markus
--

Aaron Bono wrote:
10/4/06, *Erik Jones* <erik (AT) myemma (DOT) com <mailto:erik (AT) myemma (DOT) com>
wrote:

There is one non-SQL related reason that I like to be able to order
columns, at least the way they are displayed whenever the table is
described: human comprehension. For example, I like to group all
keys
in a table before data, that includes primary as well as foreign
keys.
So, say I'm building on to an existing application and I need to do an
ALTER TABLE on an existing table to add a foreign key to an existing
table. I'd like that key to be listed with the other keys, but
presently that's not possible in a simple way and, to be honest, I
usually just go without as the process you've described below is too
prone to user (human) error when dealing with live, sensitive data for
me to want to mess with it.
--
Ah, but it is possible if you use views.

I recommend you build views and query off them. Then you can control
the order the columns will appear.
Which would be great if I didn't have (many) thousands of lines of code
that already use the tables. Besides, this is no where near a 'make or
break' thing. It's just a matter of aesthetic preference.

04 2006 18:37, / Erik Jones *:
Aaron Bono wrote:
10/4/06, *Erik Jones* <erik (AT) myemma (DOT) com <mailto:erik (AT) myemma (DOT) com>>
wrote:

There is one non-SQL related reason that I like to be able to order
columns, at least the way they are displayed whenever the table is
described: human comprehension. For example, I like to group all
keys
in a table before data, that includes primary as well as foreign
keys.
So, say I'm building on to an existing application and I need to do
an ALTER TABLE on an existing table to add a foreign key to an existing
table. I'd like that key to be listed with the other keys, but presently
that's not possible in a simple way and, to be honest, I usually just go
without as the process you've described below is too prone to user
(human) error when dealing with live, sensitive data for me to want to
mess with it.
--
Ah, but it is possible if you use views.

I recommend you build views and query off them. Then you can control
the order the columns will appear.

Which would be great if I didn't have (many) thousands of lines of code
that already use the tables. Besides, this is no where near a 'make or
break' thing. It's just a matter of aesthetic preference.

Alright, you could play with something like:

UPDATE pg_attribute SET attnum = <your number of orderingwhere
attrelid=<your tableoidand attname='<your column name>';

but do some research of possible bad side effects.

** 05 2006 16:31, / Tom Lane *:
Achilleas Mantzios <achill (AT) matrix (DOT) gatewaynet.comwrites:
Alright, you could play with something like:

UPDATE pg_attribute SET attnum = <your number of orderingwhere
attrelid=<your tableoidand attname='<your column name>';

That's guaranteed to break his table, because the physical storage
of the rows won't have changed. (To name only the most obvious
problem)

Thanx for the info, altho i warned the guy to search before act.

regards, tom lane

(end of broadcast)
TIP 5: don't forget to increase your free space map settings

Wed, 04, 2006 at 11:20:07AM -0500, Aaron Bono wrote:

So do it as needed and convert your application slowly.

You don't even need to do that.

ALTER TABLE tablename RENAME T tablename_real;

CREATE VIEW tablename [&c.]

Now the view looks to the application just like the old table. If
you want to insert &c., you put some rules there.

A

Mon, 09, 2006 at 11:01:17AM -0500, Aaron Bono wrote:
10/5/06, Andrew Sullivan <ajs (AT) crankycanuck (DOT) cawrote:
>you want to insert &c., you put some rules there.
^^^^^^^^^^^^^^^^^^^^

If you do this you need to make the view updateable or
inserts/updates/deletes will break.

That's what that "some rules" part means.

A