Re: [SQL] i have table
On Mon, Oct 09, 2006 at 11:01:17AM -0500, Aaron Bono wrote: On 10/5/06, Andrew Sullivan [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] i have table
On 10/5/06, Andrew Sullivan [EMAIL PROTECTED] 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.Ifyou 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==
Re: [SQL] i have table
Στις Τετάρτη 04 Οκτώβριος 2006 18:37, ο/η Erik Jones έγραψε: Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
Achilleas Mantzios [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] i have table
Στις Πέμπτη 05 Οκτώβριος 2006 16:31, ο/η Tom Lane έγραψε: Achilleas Mantzios [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] i have table
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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
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 [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] i have table
On 10/4/06, Erik Jones [EMAIL PROTECTED] wrote: There is one non-SQL related reason that I like to be able to ordercolumns, at least the way they are displayed whenever the table isdescribed:human comprehension.For example, I like to group all keysin 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 anALTER TABLE on an existing table to add a foreign key to an existingtable.I'd like that key to be listed with the other keys, butpresently 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 tooprone to user (human) error when dealing with live, sensitive data forme 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==
Re: [SQL] i have table
Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
On 10/4/06, Erik Jones [EMAIL PROTECTED] wrote: Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 codethat already use the tables.Besides, this is no where near a 'make orbreak' 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==
Re: [SQL] i have table
On 10/4/06 12:20 PM, Aaron Bono [EMAIL PROTECTED] wrote: On 10/4/06, Erik Jones [EMAIL PROTECTED] wrote: Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] i have table
On 10/4/06, Daryl Richter [EMAIL PROTECTED] wrote: On 10/4/06 12:20 PM, Aaron Bono [EMAIL PROTECTED] wrote: On 10/4/06, Erik Jones [EMAIL PROTECTED] wrote: Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 oneapplication crash because some developer didn't understand that columns in arelation (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==
Re: [SQL] i have table
On Wed, 2006-10-04 at 13:02, Aaron Bono wrote: On 10/4/06, Daryl Richter [EMAIL PROTECTED] wrote: On 10/4/06 12:20 PM, Aaron Bono [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] i have table
--- Penchalaiah P. [EMAIL PROTECTED] 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...
ThanksRegards
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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] i have table
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'::regclass),
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 TimesInfinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy Utilities | Retail
Distribution | Government
Tel +91-80-5193-(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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] i have 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] i have table
Στις Τρίτη 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.
ThanksRegards
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-(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 [EMAIL PROTECTED] 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
[EMAIL PROTECTED] and delete this email from your records.
--
Achilleas Mantzios
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] i have table
On 10/3/06 6:47 AM, Penchalaiah P. [EMAIL PROTECTED] 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... ThanksRegards Penchal reddy | Software Engineer -- Daryl http://itsallsemantics.com I¹m afraid of the easy stuff its always harder than it seems -- Bill Hampton, 2006 ---(end of broadcast)--- TIP 6: explain analyze is your friend
