Re: Update function

Had to do something quite similiar recently, so here goes...

-- find one of each 6-digit start combinations; note that sorting makes
-- sure that 123456,12345600000000 comes before 123456,12345600000001 and is
-- therefore selected by 'distinct' with preference
-- the strange hack with 'order by' is required to make 'distinct' happy
SELECT DISTINCT ON (substr(code, 1, 6)) code INTO tmpcodes FROM products
WHERE code LIKE '25%' ORDER BY substr(code, 1, 6), code;

-- index temp table, may be large
CREATE INDEX tmpcodes_idx ON tmpcodes(code);

-- delete duplicates
DELETE FROM products WHERE code LIKE '25%' AND NOT EXISTS
(SELECT 1 FROM tmpcodes WHERE tmpcodes.code=products.code);

-- unify remaining codes
UPDATE products SET code=substr(code, 1, 6) || '0000000'
WHERE code LIKE '25%';

-- drop temp table
DROP TABLE tmpcodes;

In the future you want to make sure that this does not happen again, so
create a unique index to prevent inserting 252345600000001 when 252345600000000
exists:

-- function to extract first 6 chars
CREATE FUNCTION barcode6(text) RETURNS text AS 'SELECT substr($1,1,6);'
LANGUAGE 'sql' WITH (iscachable,isstrict);

-- partial index on first 6 chars; requires Postgres >=7.2 I think
CREATE UNIQUE INDEX barcode6_idx ON products (barcode6(code))
WHERE code LIKE '25%';

Regards
--
Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de> /| \__
The past: Smart users in front of dumb terminals /_|____\
_/\ | __)
$ ./configure \\ \|__/__|
checking whether build environment is sane... yes \\/___/ |
checking for AIX... no (we already did this) |