Select * with specific alias [syntax]

I want to use select * t开发者_高级运维o select all the fields from a table but I also want to use an alias on just one field. Is it possible? If so what would the syntax be.

Example

select *,
item1 as hats
from factory

I have not been able to make anything like this work, Thanks!

I just tried

select *, item1 as hats from factory

on mysql and postgres and on both DBMS it runs fine. Only take into consideration that you get two columns with item1. One column named item1 and one named hats.

That should work, but keep in mind that your item1 column will be duplicated!

Suppose your table has the columns (id, item1, item2) then your proposed select will return (id,item1,item2,hats).

It's valid in MS Sql Server as well but the column you are alias'ng will be duplicated. Tried the query in Sql server 2005 and it did worked.

select *,col2 as 'customcol' from table1

Yes, it is possible to use that format however, use of Select * is not recommend. It is better to enumerate the columns you want. Some database products may balk if the list of columns in combination with your aliased column produces a duplicate column name. However, again, you can solve this by enumerating the columns.