pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedi have table
19+ messages / 11 participants
[nested] [flat]
* i have table
@ 2006-10-03 10:47 Penchalaiah P. <penchalaiahp@infics.com>
0 siblings, 5 replies; 19+ messages in thread
From: Penchalaiah P. @ 2006-10-03 10:47 UTC (permalink / raw)
To: pgsql-sql
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
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-03 10:59 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
4 siblings, 0 replies; 19+ messages in thread
From: A. Kretschmer @ 2006-10-03 10:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-03 11:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
4 siblings, 0 replies; 19+ messages in thread
From: Achilleas Mantzios @ 2006-10-03 11:00 UTC (permalink / raw)
To: pgsql-sql
Στις Τρίτη 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-03 11:49 Daryl Richter <daryl@eddl.us>
parent: Penchalaiah P. <penchalaiahp@infics.com>
4 siblings, 0 replies; 19+ messages in thread
From: Daryl Richter @ 2006-10-03 11:49 UTC (permalink / raw)
To: Penchalaiah P. <penchalaiahp@infics.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 09:22 Markus Schaber <schabi@logix-tt.com>
parent: Penchalaiah P. <penchalaiahp@infics.com>
4 siblings, 1 reply; 19+ messages in thread
From: Markus Schaber @ 2006-10-04 09:22 UTC (permalink / raw)
To: pgsql-sql
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
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 14:23 Erik Jones <erik@myemma.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 19+ messages in thread
From: Erik Jones @ 2006-10-04 14:23 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 14:54 Aaron Bono <postgresql@aranya.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 19+ messages in thread
From: Aaron Bono @ 2006-10-04 14:54 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-sql
On 10/4/06, Erik Jones <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.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 15:37 Erik Jones <erik@myemma.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 19+ messages in thread
From: Erik Jones @ 2006-10-04 15:37 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 16:20 Aaron Bono <postgresql@aranya.com>
parent: Erik Jones <erik@myemma.com>
1 sibling, 2 replies; 19+ messages in thread
From: Aaron Bono @ 2006-10-04 16:20 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-sql
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.
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
==================================================================
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 16:37 Daryl Richter <daryl@eddl.us>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 19+ messages in thread
From: Daryl Richter @ 2006-10-04 16:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 18:02 Aaron Bono <postgresql@aranya.com>
parent: Daryl Richter <daryl@eddl.us>
0 siblings, 1 reply; 19+ messages in thread
From: Aaron Bono @ 2006-10-04 18:02 UTC (permalink / raw)
To: Daryl Richter <daryl@eddl.us>; +Cc: pgsql-sql
On 10/4/06, Daryl Richter <daryl@eddl.us> wrote:
>
> 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.
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
*.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-04 20:11 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 19+ messages in thread
From: Scott Marlowe @ 2006-10-04 20:11 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Daryl Richter <daryl@eddl.us>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-05 01:23 operationsengineer1@yahoo.com
parent: Penchalaiah P. <penchalaiahp@infics.com>
4 siblings, 0 replies; 19+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-10-05 01:23 UTC (permalink / raw)
To: Penchalaiah P. <penchalaiahp@infics.com>; pgsql-sql
--- "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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-05 07:32 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 19+ messages in thread
From: Achilleas Mantzios @ 2006-10-05 07:32 UTC (permalink / raw)
To: pgsql-sql
Στις Τετάρτη 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-05 13:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2006-10-05 13:31 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-05 15:15 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Achilleas Mantzios @ 2006-10-05 15:15 UTC (permalink / raw)
To: pgsql-sql
Στις Πέμπτη 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-05 15:29 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 19+ messages in thread
From: Andrew Sullivan @ 2006-10-05 15:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-09 16:01 Aaron Bono <postgresql@aranya.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 19+ messages in thread
From: Aaron Bono @ 2006-10-09 16:01 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On 10/5/06, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>
> 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.
If you do this you need to make the view updateable or
inserts/updates/deletes will break.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: i have table
@ 2006-10-10 18:30 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 19+ messages in thread
From: Andrew Sullivan @ 2006-10-10 18:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2006-10-10 18:30 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-03 10:47 i have table Penchalaiah P. <penchalaiahp@infics.com>
2006-10-03 10:59 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-03 11:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-03 11:49 ` Daryl Richter <daryl@eddl.us>
2006-10-04 09:22 ` Markus Schaber <schabi@logix-tt.com>
2006-10-04 14:23 ` Erik Jones <erik@myemma.com>
2006-10-04 14:54 ` Re: i have table Aaron Bono <postgresql@aranya.com>
2006-10-04 15:37 ` Re: i have table Erik Jones <erik@myemma.com>
2006-10-04 16:20 ` Re: i have table Aaron Bono <postgresql@aranya.com>
2006-10-04 16:37 ` Re: i have table Daryl Richter <daryl@eddl.us>
2006-10-04 18:02 ` Re: i have table Aaron Bono <postgresql@aranya.com>
2006-10-04 20:11 ` Re: i have table Scott Marlowe <smarlowe@g2switchworks.com>
2006-10-05 15:29 ` Re: i have table Andrew Sullivan <ajs@crankycanuck.ca>
2006-10-09 16:01 ` Re: i have table Aaron Bono <postgresql@aranya.com>
2006-10-10 18:30 ` Re: i have table Andrew Sullivan <ajs@crankycanuck.ca>
2006-10-05 07:32 ` Re: i have table Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-05 13:31 ` Re: i have table Tom Lane <tgl@sss.pgh.pa.us>
2006-10-05 15:15 ` Re: i have table Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-05 01:23 ` operationsengineer1@yahoo.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox