Update an SQL table with its own rows with PostgreSQL

Question

I want to try to update a postgresql table with it's own values.

This table contains an overview of the products sold by year and month.

CREATE TABLE sales
(
  sector character(3),
  brand character(4),
  product character(16),
  syear integer,
  smonth integer,
  units_sold integer,
  units_sold_year integer,
  CONSTRAINT pk_sales_id PRIMARY KEY (sector, brand, product, syear, smonth)
);

INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 0, 9, 0); /* The month 0 is the whole year */
INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 1, 4, 0);
INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 2, 5, 0);
INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('ALL', 'ABE', '71012', 2010, 0, 9, 10);
...

I've added the column 'units_sold_year' because I need to be able to do very quick requests on this table (I'd otherwise have to do subquerys) and I'm trying to fill it.

Here's the update request I've built so far but it seems to run in an infinite loop :

UPDATE sales set units_sold_year = (SELECT units_sold_year FROM sales as s WHERE sector = 'ALL' and s.smonth = 0 and s.brand = su.brand and s.product = su.product and s.syear = su.syear)
FROM sales su
where su.syear = 2010
and su.brand = 'ABE' and su.product = '71012';

Is it possile to update a table with its own rows like this ?

Answer 1

Your query looks good (even if I disagree with the requirement)

I would change the order of your subquery, though, to match your PRIMARY KEY index. I believe that with a compound index like what you have, Postgres will evaluate in order of the index.

So your PK index is:

(sector, brand, product, syear, smonth)

and your subquery should be:

...WHERE sector = 'ALL' and s.brand = su.brand and s.product = su.product and s.syear = su.syear and s.smonth = 0...

With composite indexes, order matters for queries.

If you have a composite index on columns (col0, col1, col2), this index will benefit queries that do:

select * from tablex where col0 = a
select * from tablex where col0 = a and col1 = b
select * from tablex where col0 = a and col1 = b and col2 = c

but it will not be used for a query that does:

select * from tablex where col1=b and col2=c

So it would be redundant (and probably performance impeding) to create indexes on (col0), (col0, col1), and (col0, col1, col2).

Answer 2

The rest of the answers are great, but one thing I noticed is that you are using char(n). Generally that is a bad data type to use because it pads out. Consider using varchar instead.