Related link: http://www.adams1.com/pub/russadam/upccode.html

CD Baby has UPC or EAN barcodes in our database for most albums. Problem is : we let our clients enter their barcode themselves, and we had people entering invalid codes! (1234567890, etc)

Here’s a PL/pgSQL function for PostgreSQL databases that, when called in your table constraints, will not allow invalid barcodes in your database anymore.

CREATE OR REPLACE FUNCTION valid_barcode(barcode text) RETURNS boolean AS $function$
DECLARE
  b text;
  odd int;
  even int;
  s int;
BEGIN
  IF barcode IS NULL THEN
    return NULL;
  END IF;
  IF LENGTH(barcode) < 12 OR LENGTH(barcode) > 13 THEN
    return false;
  END IF;
  -- normalize UPC and EAN to both be 13 digits
  IF LENGTH(barcode) = 12 THEN
    b = '0' || barcode;
  ELSE
    b = barcode;
  END IF;
  -- sum of odd digits times 3, plus sum of even digits
  even = CAST(SUBSTR(b, 1, 1) AS int) + CAST(SUBSTR(b, 3, 1) AS int) + CAST(SUBSTR(b, 5, 1) AS int) + CAST(SUBSTR(b, 7, 1) AS int) + CAST(SUBSTR(b, 9, 1) AS int) + CAST(SUBSTR(b, 11, 1) AS int);
  odd = CAST(SUBSTR(b, 2, 1) AS int) + CAST(SUBSTR(b, 4, 1) AS int) + CAST(SUBSTR(b, 6, 1) AS int) + CAST(SUBSTR(b, 8, 1) AS int) + CAST(SUBSTR(b, 10, 1) AS int) + CAST(SUBSTR(b, 12, 1) AS int);
  s = (3 * odd) + even;
  -- remainder to nearest 10 should be same as last check digit
  IF (CAST((CEIL(CAST(s AS float8) / 10) * 10) AS int) % s) = CAST(SUBSTR(b, 13, 1) AS int) THEN
    return true;
  ELSE
    return false;
  END IF;
END;
$function$ LANGUAGE plpgsql;

In your database table, then, you just need two things:
barcode char(13) (of course)
… and at the end of the table definition …
CONSTRAINT bad_barcode CHECK (valid_barcode(barcode))

That will not allow any invalid UPC/EAN barcodes in your database.

I’m a PL/pgSQL newbie - lemme have it - how to improve this?