News for hackers. Stuff that matters.
Re:Selecting All Columns Except One in PostgreSQL by scrollaway on Saturday May 19, 2018 @10:38AM (#17108046)
The result format of select * is unpredictable unless you are guaranteed to know your current schema. And if you know your current schema and want all columns, for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star.

For example: Let's say you have a piece of code that does: id, value = query("select * from tbl").fetch_one(). Now that works great if you know you have exactly the columns id and value in there. But let's say they swap places (possible in mysql), or a third column is added, and you don't update your code alongside it. Now that code breaks. Had you done "select id, value from tbl", it wouldn't break.

PS: The sibling answers are also all correct; there's more reasons not to use select * outside of the shell.

Re:Selecting All Columns Except One in PostgreSQL by grogers on Saturday May 19, 2018 @10:58AM (#17108120)
Select * is useful if you are adding columns. You can either read the column or supply a default if the column doesn't exist. Doing this by having two queries naming different lists of columns is much worse, although it's unavoidable for mutations AFAIK.
Re:Selecting All Columns Except One in PostgreSQL by terandle on Saturday May 19, 2018 @10:52AM (#17108102)
Gotcha yes for sure in this example you would want to use the column names. In my database code I extract values by referencing the column names - not by their position. So neither swapping or adding a new column breaks the code.

Well thank you for explaining your reasons.

Re:Selecting All Columns Except One in PostgreSQL by anderse on Saturday May 19, 2018 @10:49AM (#17108090)
Do you mean your database driver doesn’t supply you with field names, that you rely on order to map up data?
Re:Selecting All Columns Except One in PostgreSQL by tejasmanohar on Monday May 21, 2018 @06:01PM (#17122232)
I guess I often rely on column order in Go, given Rows.Scan() [0]... so `select ` would be scary since someone may ALTER the table.

Maybe that's the antipattern! But it's also a feature so I feel like you're better off avoiding the . Also, if someone adds additional big columns, you may incur additional, unnecessary cost

[0]: https://golang.org/pkg/database/sql/#Rows.Scan

Re:Selecting All Columns Except One in PostgreSQL by scrollaway on Saturday May 19, 2018 @10:54AM (#17108109)
Your driver can probably introspect, yes. You can also probably introspect yourself if you want to. But all this to be less explicit about what you're querying seems like a waste, doesn't it?
Re:Selecting All Columns Except One in PostgreSQL by paulmd on Saturday May 19, 2018 @10:51AM (#17108098)
> for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star.

But doctor, it hurts when I [don't use an ORM/ORM-generated queries]

Re:Selecting All Columns Except One in PostgreSQL by scrollaway on Saturday May 19, 2018 @10:53AM (#17108104)
If you're not using an ORM you especially shouldn't be using the star-op in code.

Don't litter in the streets, don't litter in your code. If you make an unmaintainable mess and it hurts, you are entirely responsible for owning it.