move the position of column after alter table

[aneev]

Hi,
this is a stupid question but still I am curious to know. when I alter the table for adding an additional column to the table, can I add the column into a specific position? Say I have columns
a varchar(20)
b integer
d boolean

If I add a column c varchar(20) can I add after column b ? By default the column will be added at the end.

 

[stoolpigeon]

If someone knows better they can correct me- but I do not believe you can add a column in another place. Probably this is because it does not really matter.

Column order in the database doesn't affect how data comes out- the query does.

The only situations I can think of off the top of my head where you have to know the order in the table is an insert where you don't specify the columns or a 'select *' type query.

Personally I avoid both so I never care what order my columns have.

I'm not db guru though - so if I'm wrong I would love to learn why.

 

[aneev]

I wanted this option for the insert as my html form order differs from the one in the table. While inserting it's easy for me to look at the form and insert it without worriying about the order of the column. anyway, as you said this is not that important.
You mean to say, you specify each column name in the insert clause? I normally used to do insert into table values(...); Is it not a good practice? If so, let me know I would like to correct myself

 

[sleipnir214]

You can specify the columns to which data is inserted. For example, the query:

INSERT INTO foo (columnname2, columnname1) VALUES ('a', 'b');

will insert the values 'a' and 'b' into columns named 'columnname2' and 'columnname1', respectively.

Explicitly stating the column names is required when you are not inserting data into every column of a table (using my example above, if the table 'foo' also had a column named 'c'), or when you when it is more convenient to construct a query that adds data to columns in an order different from the order they appear in the table (as would be my example above, were the order of the columns in the table 'columnname2', then 'columnname1')

If you are going to insert data into every column, and your insert data appears in the same order they columns appear in the table, you do not need to specify the column names.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[stoolpigeon]

I don't know if it matters from a performance perspective. (writing out column names for an insert) I do it for a couple reasons.

One is so I don't get confused. It helps me to have them all spelled out.

The second is because of what sleipnir mentions above. I don't do a lot of inserts where I am covering every column in the table. So I have to state which columns I want. (I use a lot of default values, auto incrementing, that kind of thing.)