Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is that in products_active, status=1
, and products_inactive gets all the other statuses.
There is a large query which joins a bunch of tables, of which this is the first part:
SELECT
products.id, products.status, products.brand_id, products.name, products.description, products.data, products.website,
products.packaging, products.container, products.country_of_origin, products.category_id, products.product_type_id, products.tsv_keywords,
COUNT(prices.id) as prices_count,
ROUND(AVG(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as avg_price,
ROUND(MAX(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as high_price,
ROUND(MIN(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as low_price,
ts_rank(tsv_keywords, plainto_tsquery('merlot')) as rank,
ROUND(AVG(ST_Distance(ST_GeographyFromText('SRID=4326;POINT(0.001 0.001)'),ST_GeographyFromText('SRID=4326;POINT(' || stores.longitude || ' ' || stores.latitude || ')')))) AS distance
FROM
products
JOIN product_types ON products.product_type_id = product_types.id
JOIN categories ON products.category_id = categories.id
JOIN prices ON prices.product_id = products.id
JOIN currencies ON prices.currency_id = currencies.id
JOIN stores ON prices.store_id = stores.id
JOIN brands ON products.brand_id = brands.id
JOIN merchants ON stores.merchant_id = merchants.id
JOIN manufacturers ON brands.manufacturer_id = manufacturers.id
, delivery_zones
WHERE ...
(full query here: http://pastebin.com/VjJPTQWj)
The problem is: note the FROM products...
part, if I replace this with FROM products_active AS products
, the query errors out with:
ERROR: column "products.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: products.id, products.status, products.brand_id, products....
The two tables (products and products_active) have identical structures! They are inherited from one another!
Am I missing a subtlety in aliasing table names?
products
it's:"products_pkey" PRIMARY KEY, btree (id)
, and for products_active:"index_products_on_active_id" UNIQUE, btree (id)
GROUP BY
on ANY column that's not used in an aggregate. You should have status, brand_id, name, description, etc ALL in yourGROUP BY
clause. PostgreSQL is just doing something special to let you get away with only having the id column there. (I'm guessing it's because it's thePRIMARY KEY
.. but again, someone else can answer authoritatively)UNIQUE INDEX
is not the same as aUNIQUE CONSTRAINT
, which I'm sure is also factoring into the behavior. (CONSTRAINT
s are probably treated differently thanINDEXE
s) They certainly behave very similarly, but ... Please see stackoverflow.com/questions/23542794/…