Hi. If I'm using psql, and type for example:
UPDATE my_table SET my_field
(with a trailing space)
and then hit Tab, it will expand that to an =, and then another tab will expand to DEFAULT, so that I then have:
UPDATE my_table SET my_field = DEFAULT
If I'm tabbing out in this situation, it's going to be after the =, and I will have typed "myreal"[tab] in the vain hope that psql will complete that to "myreallylongfieldname," but instead it gets replaced with DEFAULT.
Yeah, it is especially annoying to delete what I actually typed to replace it with something else. I've been irked by that before. I think the general behavior of replacing something already typed with (what it believes to be) the only proper completion is part of the underlying readline/libedit library, not something psql goes out of its way to do.
So I'm curious if this is intended behavior, if it's considered useful, and/or if it's a placeholder for something in the future that will be useful. Also, is this new, as I've never noticed it before?
The tab completion doesn't have a SQL parser/analyzer, it is just driven of general rules of looking at the proceeding N words. In this case, it is hitting the rule for "SET anything TO", which is intended to catch the setting of parameters, it is only accidentally hitting on the SET part of UPDATE statements.
This goes back at least to 9.3.
We could improve it by making a higher priority rule which looks back a few more words to:
UPDATE <tablename> SET <colname> TO
But what would we complete with? Any expression can go there, and we can't make it tab complete any arbitrary expression, like function names or literals. If we tab complete, but only with a restricted set of choices, that could be interpreted as misleadingly suggesting no other things are possible. (Of course the current accidental behavior is also misleading, then)
If we are willing to offer an incomplete list of suggestions, what would they be? NULL, DEFAULT, '(' and all the columnnames present in <tablename>, with appropriate quotes where necessary? But what to do with <tablename> doesn't actually exist as the name of a table?
Or, we could have it implement the more precise higher priority rule, and have it just refuse to offer any suggestions, but at least not delete what is already there.
Cheers,
Jeff