Thread: i have table

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi …</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have one table with 12 fields.. </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE addition_alteration_memo</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  addition_alteration_memo int8 NOT NULL DEFAULT
nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclass),</span></font><pclass="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  cda_no varchar(7) NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  week numeric,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  sheet_no numeric,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  serial_no numeric,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  date date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  dr_no varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  amount numeric,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  memo_no varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  memo_date date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  no_instalments numeric,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  instalment_rate numeric) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">now I want to add one more field in this table.. but that field has to come next to cda_no.. I mean
asa 3<sup>rd</sup> field.. If I am adding that field it is coming last field … </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">may I know how it is possible to that table…</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Thanks  &  Regards</span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Penchal reddy </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Software Engineer           </span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Infinite Computer Solutions </span></font></b><b><font color="red" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Exciting Times…Infinite Possibilities... </span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">SEI-CMMI level 5 </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">| </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">ISO 9001:2000</span></font><font color="navy"><span style="color:navy"></span></font></b><p
class="MsoNormal"style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">IT SERVICES </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold">
BPO                                                                                                                                                                          
</span></font><fontcolor="navy"><span style="color:navy"></span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><fontcolor="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">Telecom </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Finance</span></font></b><b><font color="red"
face="Tahoma"size="1"><span style="font-size: 
8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Healthcare </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:red;
font-weight:bold">| </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Manufacturing</span></font></b><b><font
color="red"face="Tahoma" size="1"><span style="font-size: 
8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Energy & Utilities </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Retail& Distribution
</span></font></b><b><fontcolor="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><fontcolor="navy"
face="Tahoma"size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Government                                                        </span></font><font
color="navy"><spanstyle="color:navy"></span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><fontcolor="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">Tel +91-80-5193-0000(Ext:503)</span></font></b><b><font color="red" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Fax  +91-80-51930009 </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Cell No  +91-9980012376</span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold">www.infics.com</span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">          </span></font><font color="navy"><span style="color:navy"></span></font></b><p
class="MsoNormal"style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:gray;font-weight:bold">Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/
orits Customers and is intended for use only by the individual or entity to which it is addressed, and may contain
informationthat is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended
recipientor it appears that this mail has been forwarded to you without proper authority, you are notified that any use
ordissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately
at</span></font></b><b><fontcolor="black" face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:black;font-weight:bold"> </span></font></b><b><u><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:gray;
font-weight:bold">info.in@infics.com </span></font></u></b><b><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:gray;font-weight:bold">and delete this mail from your records.</span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div><table><tr><td bgcolor="#ffffff"><font color="#000000">Information transmitted by this
e-mailis proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the
individualor the entity to which it is addressed, and may contain information that is privileged, confidential or
exemptfrom disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been
forwardedto you without proper authority, you are notified that any use or dissemination of this information in any
manneris strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email
fromyour records.<br /></font></td></tr></table> 

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
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Στις Τρίτη 03 Οκτώβριος 2006 13:47, ο/η Penchalaiah P. έγραψε:
> Hi ...
>
> I have one table with 12 fields..
>
>
>
> CREATE TABLE addition_alteration_memo
>
> (
>
>   addition_alteration_memo int8 NOT NULL DEFAULT
> nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclas
> s),
>
>   cda_no varchar(7) NOT 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 Times...Infinite Possibilities...
>
>
> SEI-CMMI level 5 | ISO 9001:2000
>
> IT SERVICES | BPO
>
>
> 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@infics.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@infics.com and delete this email from your records.

--
Achilleas Mantzios

On 10/3/06 6:47 AM, "Penchalaiah P." <penchalaiahp@infics.com> wrote:

>
> Hi ...
>
> I have one table with 12 fields..
>
>
> 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 ...
>

The columns in a relation (table) are not ordered, so this question makes no
sense.  Why do you feel it necessary to have that field "next to" the other?
>
> may I know how it is possible to that table...
>
> Thanks  &  Regards
>
> Penchal reddy | Software Engineer
>

--
Daryl
http://itsallsemantics.com

"I¹m afraid of the easy stuffŠ its always harder than it seemsŠ"
-- Bill Hampton, 2006

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 * FROM 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
FROM table" or create a View.

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

> may I know how it is possible to that table…

If you _really_ want to do that despite what I wrote above, you have
several possibilities:

- COPY the table to some file, drop the table, recreate the table with the desired new column order, and then COPY the
tableback 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


--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

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 * FROM 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
> FROM table" or create a View.
>
> All relevant SQL constructs (SELECT, INSERT, UPDATE, COPY, etc.) let you
> specify the columns explicitly to guarantee a given order.
>
>   
>> may I know how it is possible to that table…
>>     
>
> If you _really_ want to do that despite what I wrote above, you have
> several possibilities:
>
> - COPY the table to some file, drop the table, recreate the table with
>   the desired new column order, and then COPY 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
>
>
>   


-- 
erik jones <erik@myemma.com>
software development
emma(r)

Aaron Bono wrote:
> On 10/4/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.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.

-- 
erik jones <erik@myemma.com>
software development
emma(r)

On 10/4/06 12:20 PM, "Aaron Bono" <postgresql@aranya.com> wrote:

> On 10/4/06, Erik Jones <erik@myemma.com> wrote:
>> 
>> Aaron Bono wrote:
>>> On 10/4/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.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.

Ah, but it *is* a "make or break thing."  I have seen more than one
application crash because some developer didn't understand that columns in a
relation (table) have no defined order.

This (along with its sister axiom that rows have no defined order) is one of
the most commonly misunderstood aspects of relational databases.

> 
> 
> 
> So do it as needed and convert your application slowly.
> 
> I just name my views as table_name_vw so all you have to do is modify your
> queries to hit the _vw instead of just the table.  That shouldn't take much
> time to refactor.
> 
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================

--
Daryl
http://itsallsemantics.com

On Wed, 2006-10-04 at 13:02, Aaron Bono wrote:
> On 10/4/06, Daryl Richter <daryl@eddl.us> wrote:
>         On 10/4/06 12:20 PM, "Aaron Bono" <postgresql@aranya.com>
>         wrote:

>         >
>         > So do it as needed and convert your application slowly.
>         >
>         > I just name my views as table_name_vw so all you have to do
>         is modify your 
>         > queries to hit the _vw instead of just the table.  That
>         shouldn't take much
>         > time to refactor.
> 
>  
> I wasn't condoning using select * in your application - if an
> application is riddled with that, it is time to start fixing the
> problem because it WILL bite you eventually.  I never let my team put
> select * in any queries that end up in the application code - bad
> stuff that! 
> 
> Of course sometimes, especially when you are doing quick throw away
> queries, select * is nice and controlling the order is handy.  It is
> also beneficial when using a generic database tool which will almost
> definitely do a select *. 

Actually, the one time I've written an application with select * in it
was when I wrote some simple, generic app that used select * to root out
the layout of the table and make a simple edit screen for any generic
table in postgresql.  It used select * from table limit 1 to get the
layout, and using libpq was able to find the type of each field and
thereby produce a proper update / insert query.

But any REAL application should never do that, I agree.

--- "Penchalaiah P." <penchalaiahp@infics.com> wrote:

> 
> Hi ...
> 
> I have one table with 12 fields..
> 
> 
> 
> CREATE TABLE addition_alteration_memo
> 
> (
> 
>   addition_alteration_memo int8 NOT NULL DEFAULT
>
nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclas
> s),
> 
>   cda_no varchar(7) NOT 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...
> 
> 
> 
> Thanks  &  Regards
> 
> Penchal reddy | Software Engineer          

Penchal,

yes, you can do this, but it isn't necessary beyond
making you feel more comfortable when you look at your
column names in something like pgadmin3.

i don't have my code in front of me, but what you do
is export your table definitions into sql text.  there
is a statement you can put at the beginning of the
table that deletes it if it already exists.  

update the text file with the columns in order and
then import the sql table definitions.

your original table is deleted (if it exists) and the
new table is generated in the order you want it.

when i get some time on the computer where i've done
this, i will post an example.

oe1

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Στις Τετάρτη 04 Οκτώβριος 2006 18:37, ο/η Erik Jones έγραψε:
> Aaron Bono wrote:
> > On 10/4/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.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 ordering> where
attrelid=<your tableoid> and attname='<your column name>';

but do some research of possible bad side effects.
--
Achilleas Mantzios

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> Alright, you could play with something like:

> UPDATE pg_attribute SET attnum = <your number of ordering> where 
> attrelid=<your tableoid> and 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...)
        regards, tom lane

Στις Πέμπτη 05 Οκτώβριος 2006 16:31, ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> > Alright, you could play with something like:
> >
> > UPDATE pg_attribute SET attnum = <your number of ordering> where
> > attrelid=<your tableoid> and 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...)

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

--
Achilleas Mantzios

On Wed, Oct 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 TO 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

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."    --Damien Katz

On Mon, Oct 09, 2006 at 11:01:17AM -0500, Aaron Bono wrote:
> On 10/5/06, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> >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

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes