Skip to content

Instantly share code, notes, and snippets.

@mbn18
Created August 20, 2015 17:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mbn18/22e9b2a1b5755527eb0e to your computer and use it in GitHub Desktop.
Save mbn18/22e9b2a1b5755527eb0e to your computer and use it in GitHub Desktop.
Bug on PostgreSQL 9.4.4
DROP TABLE test;
SELECT * FROM test;
CREATE TABLE test (
id SERIAL,
name TEXT
);
INSERT INTO test (name) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h');
CREATE OR REPLACE FUNCTION test_perform_bug_on_unique_column()
RETURNS TEXT AS $$
DECLARE
d_code TEXT;
d_count INT;
BEGIN
d_count := 0;
LOOP
SELECT string_agg(SUBSTRING(cs.chars FROM ceil((char_length(cs.chars) * random())) :: INT FOR 1), '')
FROM generate_series(1, 1, 1) CROSS JOIN (SELECT 'abcdefgh' :: TEXT AS chars) AS cs INTO d_code;
PERFORM TRUE FROM test WHERE code=d_code;
IF (NOT FOUND) THEN
RETURN d_code;
END IF;
d_count := d_count + 1;
EXIT WHEN d_count > 500;
END LOOP;
RAISE EXCEPTION 'Failed to generate Account code';
END;
$$ LANGUAGE plpgsql;
ALTER TABLE test ADD COLUMN code TEXT DEFAULT test_perform_bug_on_unique_column() UNIQUE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment