PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create CHECK constraint based on complex algorithm

A personal identity codes consist of a string of numbers that indicates the individual’s date of birth, an individualized string, and a control character.
Example: 150600A905P

• 150600 = Date of birth 
• A = the character in the middle 
• 905 = the individualized string 
• P = Control character

  • The control character is either a number or a letter.
    The calculation formula is to divide the value of the nine-digit string made up by the date of birth and the individualized string by 31. Then the value of the division’s remainder determines the control character. Remainders are from 0->30 and the corresponding control character are [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, H, J, K, L, M, N, P, R, S, T, U, V, W, X, Y]

  • The character in the middle : The convention for the mid-character is:

    • Year of birth in 1800’s → + (the plus sign),
    • Year of birth in 1900’s → – (minus sign) and Y, X, W, V, U (capital letters)
    • Year of birth in 2000’s → A, B, C, D, E, F (capital letters).

How to create a check constraint in Postgres for the volunteer table with a function that validates a volunteer ID when a new volunteer is inserted. The ID is valid if they satisfies:

- Length = 11 characters 
- The 7th character (separator) is one of the following: +, -, A, B, C, D, E, F, X, Y, W, V, U 
- The correct control character is used

The constraint does not work as expected. What’s wrong with my logic?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    -- Create a function to calculate the control character

    CREATE OR REPLACE FUNCTION calculate_control_character(volunteer_id VARCHAR)
RETURNS CHAR AS $$
DECLARE
    dob_individualized VARCHAR(9);
    dob VARCHAR(6);
    mid_character CHAR(1);
    control_character CHAR(1);
    remainder INT;
BEGIN
    -- Extract date of birth and individualized string from the volunteer ID
    dob := SUBSTRING(volunteer_id FROM 1 FOR 6);
    dob_individualized := SUBSTRING(volunteer_id FROM 7 FOR 3);

    -- Determine the mid-character based on the year of birth
    CASE 
        WHEN dob BETWEEN '000001' AND '999999' THEN mid_character := '+';
        WHEN dob BETWEEN '00A001' AND '99F999' THEN mid_character := '-';
        ELSE mid_character := '';
    END CASE;

    -- Concatenate the date of birth and individualized string
    dob := dob || dob_individualized;

    -- Calculate the remainder
    remainder := CAST(dob AS INT) % 31;

    -- Determine the control character
    CASE remainder
        WHEN 10 THEN control_character := 'A';
        WHEN 11 THEN control_character := 'B';
        WHEN 12 THEN control_character := 'C';
        WHEN 13 THEN control_character := 'D';
        WHEN 14 THEN control_character := 'E';
        WHEN 15 THEN control_character := 'F';
        WHEN 16 THEN control_character := 'H';
        WHEN 17 THEN control_character := 'J';
        WHEN 18 THEN control_character := 'K';
        WHEN 19 THEN control_character := 'L';
        WHEN 20 THEN control_character := 'M';
        WHEN 21 THEN control_character := 'N';
        WHEN 22 THEN control_character := 'P';
        WHEN 23 THEN control_character := 'R';
        WHEN 24 THEN control_character := 'S';
        WHEN 25 THEN control_character := 'T';
        WHEN 26 THEN control_character := 'U';
        WHEN 27 THEN control_character := 'V';
        WHEN 28 THEN control_character := 'W';
        WHEN 29 THEN control_character := 'X';
        WHEN 30 THEN control_character := 'Y';
        ELSE control_character := CAST(remainder AS CHAR(1));
    END CASE;

    RETURN control_character;
END;
$$ LANGUAGE plpgsql;

    -- Add a check constraint to the Volunteer table

        ALTER TABLE Volunteer
    ADD CONSTRAINT CHK_ValidVolunteerID CHECK (
        LENGTH(ID) = 11 AND
        SUBSTRING(ID FROM 7 FOR 1) IN ('+', '-', 'A', 'B', 'C', 'D', 'E', 'F', 'X', 'Y', 'W', 'V', 'U') AND
        SUBSTRING(ID FROM 11 FOR 1) = calculate_control_character(ID)
    );

  2. 0

    What you are asking for validating a column value before insert, and if it fails than return some sort of error, in this case you can create a check constraint which calls a custom function to validate volunteer_id:
    Your table can look somewhat look like:

    CREATE TABLE volunteer (
    id SERIAL PRIMARY KEY,
    volunteer_id TEXT NOT NULL,
    CONSTRAINT chk_volunteer_id CHECK (validate_volunteer_id(volunteer_id))
);

    if table already exists than:

    ALTER TABLE volunteer
ADD CONSTRAINT chk_volunteer_id CHECK (validate_volunteer_id(volunteer_id));

    before running this you will have to check all existing data meets the criteria already or the alter statement will fail.

    Now regarding the custom function:

    CREATE OR REPLACE FUNCTION validate_volunteer_id(vol_id TEXT) 
RETURNS BOOLEAN AS $$
DECLARE
    dob TEXT;
    individualized_string TEXT;
    control_character TEXT;
    separator CHAR;
    nine_digit_string BIGINT;
    remainder INTEGER;
    valid_control_characters CHAR[];
BEGIN
    IF LENGTH(vol_id) <> 11 THEN
        RETURN FALSE;
    END IF;

    dob := SUBSTRING(vol_id FROM 1 FOR 6);
    separator := SUBSTRING(vol_id FROM 7 FOR 1);
    individualized_string := SUBSTRING(vol_id FROM 8 FOR 3);
    control_character := SUBSTRING(vol_id FROM 11 FOR 1);

    IF separator NOT IN ('+', '-', 'A', 'B', 'C', 'D', 'E', 'F', 'X', 'Y', 'W', 'V', 'U') THEN
        RETURN FALSE;
    END IF;

    nine_digit_string := (dob || individualized_string)::BIGINT;

    remainder := nine_digit_string % 31;

    valid_control_characters := ARRAY['0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
                                      'A', 'B', 'C', 'D', 'E', 'F', 'H', 'J', 'K', 'L',
                                      'M', 'N', 'P', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y'];
    IF control_character <> valid_control_characters[remainder + 1] THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

    Now when you will try to insert a new row it will be validated by the validate_volunteer_id function. If the volunteer_id does not meet the criteria, the insert will fail with a constraint violation error.

    Hope this is what you were looking for

    Login or Signup to reply.
  3. 0

    Function

    You could fix your function (errors marked with !!! ):

    CREATE OR REPLACE FUNCTION pg_temp.org(volunteer_id VARCHAR)
RETURNS CHAR AS $$
DECLARE
    dob_individualized VARCHAR(9);
    dob VARCHAR(9);  --  VARCHAR(6) was too short !!!
    mid_character CHAR(1);
    control_character CHAR(1);
    remainder INT;
BEGIN
    -- Extract date of birth and individualized string from the volunteer ID
    dob := SUBSTRING(volunteer_id FROM 1 FOR 6);
    dob_individualized := SUBSTRING(volunteer_id FROM 8 FOR 3);  -- offset 7 was wrong !!!

    -- Determine the mid-character based on the year of birth
    CASE 
        WHEN dob BETWEEN '000001' AND '999999' THEN mid_character := '+';
        WHEN dob BETWEEN '00A001' AND '99F999' THEN mid_character := '-';
        ELSE mid_character := '';
    END CASE;

    -- Concatenate the date of birth and individualized string
    dob := dob || dob_individualized;

    -- Calculate the remainder
    remainder := CAST(dob AS INT) % 31;

    -- Determine the control character
    CASE remainder
        WHEN 10 THEN control_character := 'A';
        WHEN 11 THEN control_character := 'B';
        WHEN 12 THEN control_character := 'C';
        WHEN 13 THEN control_character := 'D';
        WHEN 14 THEN control_character := 'E';
        WHEN 15 THEN control_character := 'F';
        WHEN 16 THEN control_character := 'H';
        WHEN 17 THEN control_character := 'J';
        WHEN 18 THEN control_character := 'K';
        WHEN 19 THEN control_character := 'L';
        WHEN 20 THEN control_character := 'M';
        WHEN 21 THEN control_character := 'N';
        WHEN 22 THEN control_character := 'P';
        WHEN 23 THEN control_character := 'R';
        WHEN 24 THEN control_character := 'S';
        WHEN 25 THEN control_character := 'T';
        WHEN 26 THEN control_character := 'U';
        WHEN 27 THEN control_character := 'V';
        WHEN 28 THEN control_character := 'W';
        WHEN 29 THEN control_character := 'X';
        WHEN 30 THEN control_character := 'Y';
        ELSE control_character := CAST(remainder AS CHAR(1));
    END CASE;

    RETURN control_character;
END;
$$ LANGUAGE plpgsql;

    But most of it is just noise. Boils down to:

    CREATE OR REPLACE FUNCTION calculate_control_character(volunteer_id text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN ('[0:30]={0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,H,J,K,L,M,N,P,R,S,T,U,V,W,X,Y}'::text[])
       [(left($1, 6) || substring($1, 8, 3))::int % 31];

    Basically picks an array element according to the position computed with your formula.

    Using the short syntax of standard SQL functions. See:

    Add appropriate function labels. In this case: IMMUTABLE, PARALLEL SAFE, STRICT. The first two are crucial for performance! See:

    Postgres arrays are 1-based by default. The modulo operator % returns 0-based. You can either add 1, or work with a custom-index array (cheaper). See:

    Don’t use the data type char(n). See:

    CHECK constraint

    Also a bit cheaper and shorter:

    ALTER TABLE volunteer
ADD CONSTRAINT chk_validvolunteerid CHECK (
   length(id) = 11
   AND substring(id, 7, 1) = ANY ('{+,-,A,B,C,D,E,F,X,Y,W,V,U}')
   AND right(id, 1) = calculate_control_character(id)
);

    You could even just use the expression from my condensed function directly without creating a function at all.

    Just remember to document what you are doing and why, either way.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search