Referencing column aliases in WHERE does not work in PostgreSQL. Reason is explained as SQL standard compliance. Nevertheless, MySQL and SQLite support it. Example: SELECT city AS c FROM customers WHERE c = 'Warsaw'; PGSQL ERROR: column "c" does not exist This is explained e.g. at https://stackoverflow.com/q/3241352 SOLUTION #1: Idea from https://stackoverflow.com/a/8095413 but I find this even simpler as there is no need to reference temporary view name: SELECT c FROM (SELECT *, city AS c FROM customers) AS city_alias WHERE c = 'Warsaw'; (The internal view gives access to the 'c' alias and its '*' gives access to all other columns so we have equivalend of 'FROM customers'. city_alias is necessary by definition but unused so it can be unique name obfuscated by KDb, e.g. '__kdb_customers_alias123') SOLUTION #2: Just expand the column alias 'c' back to 'customers.city': SELECT city AS c FROM customers WHERE customers.city = 'Warsaw';
Extended scope to the SELECT section. Neither this work in PostgreSQL: SELECT city as c, c || '!' FROM customers; We have the same soultions as for WHERE: 1. SELECT c, c || '!' FROM (SELECT *, city AS c FROM customers) AS city_alias or 2. SELECT customers.city as c, customers.city || '!' FROM customers
How about being standards compliant, and generating valid SQL?
> How about being standards compliant, and generating valid SQL? No implementation is compliant, as someone noted in the stackoverflow thread even PostgreSQL has deviations for convenience of users. Notable convenience additions started to appear in our functions: https://community.kde.org/Kexi/Plugins/Queries/SQL_Functions. Lack of support described in this thread is rather unfortunate for me as in practice it's nothing more than inconvenience, if one wants portability to MySQL and SQLite for example, this standard compliance is a step backward.
Dev info for the record in KDb 9f68694395b (master): in selectStatementInternal() we are kind of using the SOLUTION #2, see the code where s_where_sub is set (it is computed without aliases). KEXI visual query designer offers setting aliases for fields but not for tables so WHERE section can't be controlled from this view. Relations between are properly generated. So the problem would be mostly reproducible in KDb API and Kexi SQL view.