Hacker News Reader

Comment by bcoates | Hacker News Reader

He's missing the other half of why 'select *' is bad, which really is about the star: Selecting columns by name automatically makes almost all incompatible schema changes cause the query to fail (in an easily detected place, for an obvious reason), while still allowing non-breaking schema changes like adding or dropping columns you don't use to have exactly the same behavior as before.

View on HN

I came here to say the exact same thing.

The other nice benefit is that, when you list the fields you're retrieving, it functions like a mini-reference as you write/modify your query, so you don't have to keep switching to the schema to see if it's called "id" or "row_id", if it's "package_name" or "pkgname"...

To me, "SELECT *" almost feels like using your variables without declaring them first -- not technically wrong in many languages, but still uncomfortable-feeling.

to me SELECT * is like var in C#

I don't understand the var hatred in c#. Why type a type name twice? I can understand a function result needing to be explicitly typed but why do this?

StringBuilder stringBuilder = new StringBuilder();

That is horrendously redundant. Var is handy shorthand and should be used!!

when declare and init an obj with constructor, yes it's annoying to type the type twice and I def. use `var`

But what if you're doing something like

    var x = myOtherObjectName.y;

readability is destroyed in this case.

To provide an alternative view, in e.g. Haskell the redundancy would be solved the other way round:

    (stringBuilder :: StringBuilder) <- construct

(Assuming that there was a type class that had construct as a method and StringBuilder was an instance of that class.)

'select <star>' also doesn't always do what you expect.

I recall a problem with some Oracle views that were based on 'select <star>' queries. What we didn't realize is that the '*' is evaluated when the view is compiled, and if you later add columns to the underlying tables the view did not automatically include them.

Edit: OK I don't know how to include a literal star in text here.

Try typing 3 stars in a row: * :).

No luck

The following works without issues, for whatever reason: 'select *'

I'm going to guess you need to backslash-escape your stars.

EDIT: Nope, that wasn't it.

shakes fist at crazy home-grown not-quite-Markdown

It works if you put a * space * around your stars.

  Or you can make a *literal* line by putting two spaces before it.

The only thing is you need to be careful about where the contract is.

There's a huge difference between an application executing a sql statement:

    SELECT * FROM accounts;

and a UDF like:

    CREATE OR REPLACE FUNCTION accounts__list_all()
    RETURNS SETOF accounts
    LANGUAGE SQL AS
    $$
       SELECT * FROM accounts ORDER BY account_no;
    $$;

In the latter case the select * reduces maintenance points in your db because you have already tied the return type to the table type, so either you get to use the * or list all columns manually and the latter, given the requirement to list all every time, makes any schema changes incompatible with your API.

Can't believe this partially informed article got on HN. The point of the problem with * is as you say, when the schema changes, named column queries will still work. * with numbered columns will fail and not always in a detectable way. Article is BS.

Reply on news.ycombinator.com