Skip site navigation (1) Skip section navigation (2)

Re: Update function

From: Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de>
To: Rob <rob(at)obsidian(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Update function
Date: 2002-04-12 11:38:33
Message-ID: Pine.LNX.4.21.0204121314020.2608-100000@lothlorien.stunet2.tu-freiberg.de (view raw or flat)
Thread:
Lists: pgsql-novice
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)            |


In response to

pgsql-novice by date

Next:From: RobDate: 2002-04-12 15:42:53
Subject: Update function
Previous:From: C.Raymond ChurchDate: 2002-04-12 11:05:01
Subject: Re: Update function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group