Error Handling Guide

Overview

NominaSoft uses PostgreSQL’s SQLSTATE error code system to handle and communicate errors. The tab_error table contains a comprehensive catalog of all possible error codes and their descriptions.

Error Code Structure

SQLSTATE codes are 5-character strings organized by class:

First 2 characters: Error class (e.g., 22 = Data Exception, 23 = Integrity Constraint Violation)
Last 3 characters: Specific error within that class

Common Error Classes

Class	Category	Description
`00`	Success	Operation completed successfully
`22`	Data Exception	Invalid data format or value
`23`	Integrity Constraint	Violation of constraints (FK, unique, NOT NULL, check)
`42`	Syntax Error	SQL syntax or access rule violation
`P0`	PL/pgSQL	Procedural language errors
`53`	Resources	Insufficient system resources
`57`	Operator Intervention	Query canceled or system shutdown

Critical Errors in Payroll Calculation

The fun_act_nomina() function handles these specific errors:

SQLSTATE 22008 - Datetime Field Overflow

Triggered when: Year, month, or period parameters don’t match tab_pmtros configuration

IF wano_nomina <> wreg_pmtros.ano_nomina THEN
    RAISE EXCEPTION USING ERRCODE = 22008;
END IF;
IF wmes_nomina <> wreg_pmtros.mes_nomina THEN
    RAISE EXCEPTION USING ERRCODE = 22008;
END IF;
IF wper_nomina > 2 THEN
    RAISE EXCEPTION USING ERRCODE = 22008;
END IF;

EXCEPTION
    WHEN SQLSTATE '22008' THEN
        RAISE NOTICE 'El año, o el mes, o el período no corresponden al de PMTROS... Arréglelo Bestia';
        RETURN FALSE;

Resolution:

Check current parameters: SELECT ano_nomina, mes_nomina FROM tab_pmtros;
Call function with matching values: SELECT fun_act_nomina(2025, 1, 1);
Or update parameters if needed: UPDATE tab_pmtros SET mes_nomina = 2 WHERE ...;

The per_nomina parameter must be 1 or 2. Values > 2 trigger this error.

SQLSTATE 23502 - Not Null Violation

Triggered when: A required (NOT NULL) field receives a NULL value

EXCEPTION
    WHEN SQLSTATE '23502' THEN
        RAISE NOTICE 'Está mandando un NULO en el ID... Sea serio';
        RETURN FALSE;

Common scenarios:

Missing employee ID in function call
NULL salary value for employee
Missing concept ID in insert

Resolution: Ensure all required fields have values before calling functions

SQLSTATE 23503 - Foreign Key Violation

Triggered when: Referenced record doesn’t exist

EXCEPTION
    WHEN SQLSTATE '23503' THEN  
        RAISE NOTICE 'El Cargo no existe... Créelo y vuelva, o ni se aparezca más por acá';
        RETURN FALSE;

Common scenarios:

Employee Creation
Concept Configuration
Payroll Calculation

Attempting to create employee with non-existent position:

-- This will fail if id_cargo = 99 doesn't exist
SELECT fun_insert_emplea(
    'Juan', 'Perez', FALSE, 'Calle 1', 3001234567,
    3, 0, 0, 'O+', 25,
    99,  -- Invalid cargo ID
    5000000, '2024-01-01'
);

Fix: Create the position first:

INSERT INTO tab_cargos VALUES(99, 'Nuevo Cargo');

Setting invalid concept IDs in parameters:

-- This will fail if concept 99 doesn't exist
UPDATE tab_pmtros SET id_concep_sb = 99;

Fix: Use existing concept ID or create the concept first

Employee references deleted position:

-- If employee has id_cargo = 5 but cargo 5 was deleted
SELECT * FROM tab_emplea WHERE id_cargo NOT IN (SELECT id_cargo FROM tab_cargos);

Fix: Reassign employees before deleting positions

SQLSTATE 23505 - Unique Violation

Triggered when: Attempting to insert duplicate primary key or unique constraint

EXCEPTION
    WHEN SQLSTATE '23505' THEN  
       RAISE NOTICE 'El registro ya existe.. Trabaje bien o ábrase llaveee';
       RETURN FALSE;

Common scenarios:

Manually inserting employee with existing ID
Duplicate payroll calculation (same year/month/period/employee/concept)
Re-inserting concept with same ID

Resolution:

For employees: Use the insert function (auto-generates ID)
For payroll: The system deletes existing records before recalculation
For manual inserts: Check for existing records first

SQLSTATE 22001 - String Data Right Truncation

Triggered when: String value exceeds column length

EXCEPTION
    WHEN SQLSTATE '22001' THEN  
        RAISE NOTICE 'El nombre es muy corto.. Es de su abuelita?';
        RETURN FALSE;

This error message is misleading - it actually triggers when the string is too long, not too short. The constraint checks (LENGTH(nom_emplea) >= 3) handle minimum length.

Resolution: Shorten the input value to fit column constraints

SQLSTATE P0001 - Raise Exception

Triggered when: Insert or update operation fails unexpectedly

INSERT INTO tab_nomina VALUES(...);  
IF NOT FOUND THEN
    RAISE EXCEPTION USING ERRCODE = 'P0001';
END IF;

This is a catch-all for insert failures. Check:

Constraint violations
Data type mismatches
Permission issues

Data Exception Errors (Class 22)

Comprehensive list of data-related errors from tab_error:

22000 - Data Exception (General)

Description: Generic data exceptionCommon causes:

Invalid data format
Type conversion failures
Constraint violations

Example:

-- Attempting to insert text into numeric field
UPDATE tab_emplea SET tel_emplea = 'abc' WHERE id_emplea = 123;

22008 - Datetime Field Overflow

Description: Date/time value out of range or invalidUsed in NominaSoft for: Parameter validation in fun_act_nomina()Example:

-- Calling payroll with wrong month
SELECT fun_act_nomina(2025, 13, 1);  -- Month 13 doesn't exist

22012 - Division By Zero

Description: Arithmetic operation attempted division by zeroExample:

-- If num_diasmes is somehow 0
wval_salario / wreg_pmtros.num_diasmes  -- Error!

Prevention: Always validate denominators in calculations

22003 - Numeric Value Out Of Range

Description: Number too large or small for column typeExample:

-- Salary exceeds DECIMAL(8) limit
UPDATE tab_emplea SET val_sal_basico = 999999999;  -- Too large!

Fix: Use appropriate numeric types for your data range

22001 - String Data Right Truncation

Description: String too long for columnExample:

-- Name too long for VARCHAR constraint
SELECT fun_insert_emplea(
    'Supercalifragilisticexpialidocious...', -- Very long name
    ...
);

22P02 - Invalid Text Representation

Description: Cannot convert text to target data typeExample:

-- Attempting to cast invalid string to number
SELECT CAST('abc' AS INTEGER);  -- Error!

Integrity Constraint Errors (Class 23)

23000 - Integrity Constraint Violation (General)

Description: Generic constraint violationAll constraint violations fall under this class.

23502 - Not Null Violation

Description: Attempting to insert NULL into NOT NULL columnFrom tab_error:

Not Null Violation: Columna no permite nulos.

Example:

INSERT INTO tab_emplea (id_emplea, nom_emplea) 
VALUES (123, NULL);  -- nom_emplea is NOT NULL

All NOT NULL fields in tab_emplea:

id_emplea, nom_emplea, ape_emplea
ind_genero, dir_emplea, tel_emplea
ind_estrato, ind_est_civil, num_hijos
val_tipo_sangre, val_edad
id_cargo, val_sal_basico, fec_ingreso

23503 - Foreign Key Violation

Description: Referenced record doesn’t existFrom tab_error:

Foreign Key Violation: Violación de clave foránea.

Foreign key relationships:

Table	Column	References
tab_emplea	id_cargo	tab_cargos(id_cargo)
tab_pmtros	mes_nomina	tab_meses(id_mes)
tab_pmtros	id_concep_sb	tab_conceptos(id_concepto)
tab_pmtros	id_concep_at	tab_conceptos(id_concepto)
tab_nomina	id_emplea	tab_emplea(id_emplea)
tab_nomina	id_concepto	tab_conceptos(id_concepto)
tab_nomina	mes_nomina	tab_meses(id_mes)
tab_novedades	id_emplea	tab_emplea(id_emplea)
tab_novedades	id_concepto	tab_conceptos(id_concepto)

23505 - Unique Violation

Description: Duplicate value in unique/primary key columnFrom tab_error:

Unique Violation: Violación de restricción única.

Primary keys in NominaSoft:

tab_emplea: id_emplea
tab_cargos: id_cargo
tab_conceptos: id_concepto
tab_meses: id_mes
tab_pmtros: id_empresa
tab_nomina: (ano_nomina, mes_nomina, per_nomina, id_emplea, id_concepto)
tab_novedades: (ano_nomina, mes_nomina, per_nomina, id_emplea, id_concepto)

23514 - Check Violation

Description: Value doesn’t satisfy CHECK constraintFrom tab_error:

Check Violation: Violación de restricción CHECK.

Common CHECK constraints:

-- Employee constraints
CHECK(LENGTH(nom_emplea) >= 3)
CHECK(LENGTH(ape_emplea) >= 2)
CHECK(ind_estrato BETWEEN 1 AND 6)
CHECK(ind_est_civil BETWEEN 0 AND 4)
CHECK(val_edad >= 16)
CHECK(val_sal_basico >= 0)

-- Concept constraints
CHECK(LENGTH(nom_concepto) >= 5)
CHECK(ind_perio_pago = 'Q' OR ind_perio_pago = 'M')
CHECK(val_porcent >= 0)
CHECK(val_fijo >= 0)

-- Novelty constraints
CHECK(val_dias_trab >= 1 AND val_dias_trab <= 30)
CHECK(val_horas_trab >= 1 AND val_horas_trab <= 24)

Example violation:

-- Age below minimum
SELECT fun_insert_emplea(
    'Juan', 'Perez', FALSE, 'Calle 1', 3001234567,
    3, 0, 0, 'O+', 
    15,  -- Age 15 violates CHECK(val_edad >= 16)
    5, 5000000, '2024-01-01'
);

PL/pgSQL Errors (Class P0)

Errors specific to PostgreSQL procedural language:

P0000 - PL/pgSQL Error (General)

Description: General PL/pgSQL errorFrom tab_error:

PL/pgSQL Error: Error general en PL/pgSQL.

P0001 - Raise Exception

Description: Explicitly raised exceptionFrom tab_error:

Raise Exception: Se ejecutó RAISE EXCEPTION.

Used in NominaSoft for: Insert operation failures

INSERT INTO tab_nomina VALUES(...);
IF NOT FOUND THEN
    RAISE EXCEPTION USING ERRCODE = 'P0001';
END IF;

P0002 - No Data Found

Description: SELECT INTO returned no rowsFrom tab_error:

No Data Found: No se encontraron datos en PL/pgSQL.

Example:

SELECT nom_emplea INTO v_name 
FROM tab_emplea 
WHERE id_emplea = 999999;  -- Doesn't exist
-- Raises P0002

P0003 - Too Many Rows

Description: SELECT INTO returned multiple rowsFrom tab_error:

Too Many Rows: Se obtuvieron más filas de las esperadas.

Example:

SELECT nom_emplea INTO v_name 
FROM tab_emplea;  -- Returns all employees
-- Raises P0003 (expected single row)

Fix: Add WHERE clause or use cursor/array

P0004 - Assert Failure

Description: ASSERT statement failedFrom tab_error:

Assert Failure: Falla en aserción en PL/pgSQL.

Example:

ASSERT val_sal_basico > 0, 'Salary must be positive';

Syntax and Access Errors (Class 42)

42000 - Syntax Error or Access Rule Violation

Description: General SQL syntax errorFrom tab_error:

Syntax Error or Access Rule Violation: Error de sintaxis o violación de reglas de acceso.

42601 - Syntax Error

Description: SQL syntax errorFrom tab_error:

Syntax Error: Error de sintaxis.

Example:

SELECT * FORM tab_emplea;  -- Typo: FORM instead of FROM

42501 - Insufficient Privilege

Description: User lacks permission for operationFrom tab_error:

Insufficient Privilege: Privilegios insuficientes.

Example:

-- User without DELETE permission
DELETE FROM tab_emplea WHERE id_emplea = 123;

42804 - Datatype Mismatch

Description: Incompatible data types in operationFrom tab_error:

Datatype Mismatch: Tipos de datos incompatibles.

Example:

SELECT id_emplea + nom_emplea FROM tab_emplea;  -- Can't add number to text

System Resource Errors (Class 53)

53000 - Insufficient Resources

From tab_error:

Insufficient Resources: Recursos insuficientes.

53100 - Disk Full

From tab_error:

Disk Full: Disco lleno.

Resolution: Free up disk space or expand storage

53200 - Out Of Memory

From tab_error:

Out Of Memory: Memoria insuficiente.

Causes:

Very large result sets
Complex queries
Insufficient server RAM

53300 - Too Many Connections

From tab_error:

Too Many Connections: Demasiadas conexiones.

Resolution:

Close unused connections
Increase max_connections in PostgreSQL config
Implement connection pooling

Error Handling Best Practices

Validate inputs before function calls

Check that all required data exists:

-- Before creating employee, verify position exists
SELECT id_cargo FROM tab_cargos WHERE id_cargo = target_id;

-- Before payroll calculation, verify parameters match
SELECT ano_nomina, mes_nomina FROM tab_pmtros;

Use transactions for multi-step operations

BEGIN;
    -- Multiple related operations
    SELECT fun_insert_emplea(...);
    UPDATE tab_cargos SET ...;
COMMIT;

-- If any operation fails, all are rolled back
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Operation failed: %', SQLERRM;

Log errors for debugging

EXCEPTION
    WHEN SQLSTATE '23503' THEN
        RAISE NOTICE 'FK violation: % at %', SQLERRM, CURRENT_TIMESTAMP;
        -- Log to error table
        INSERT INTO error_log VALUES(CURRENT_TIMESTAMP, SQLSTATE, SQLERRM);

Provide user-friendly error messages

Convert SQLSTATE codes to meaningful messages:

EXCEPTION
    WHEN SQLSTATE '23502' THEN
        RETURN 'Required field is missing';
    WHEN SQLSTATE '23503' THEN
        RETURN 'Referenced record does not exist';
    WHEN SQLSTATE '23505' THEN
        RETURN 'Record already exists';

Querying Error Codes

The tab_error table provides a complete reference:

-- Search for specific error code
SELECT cod_error, name_error 
FROM tab_error 
WHERE cod_error = '23505';

-- Find all integrity constraint errors
SELECT cod_error, name_error 
FROM tab_error 
WHERE cod_error LIKE '23%'
ORDER BY cod_error;

-- Search by keyword
SELECT cod_error, name_error 
FROM tab_error 
WHERE name_error ILIKE '%foreign key%';

Get Started

Database Schema

Guides

Error Handling Guide

Overview

Error Code Structure

Common Error Classes

Critical Errors in Payroll Calculation

SQLSTATE 22008 - Datetime Field Overflow

SQLSTATE 23502 - Not Null Violation

SQLSTATE 23503 - Foreign Key Violation

SQLSTATE 23505 - Unique Violation

SQLSTATE 22001 - String Data Right Truncation

SQLSTATE P0001 - Raise Exception

Data Exception Errors (Class 22)

Integrity Constraint Errors (Class 23)

PL/pgSQL Errors (Class P0)

Syntax and Access Errors (Class 42)

System Resource Errors (Class 53)

Error Handling Best Practices

Querying Error Codes

Next Steps

Payroll Calculation

Employee Management

Build docs developers (and LLMs) love

Get Started

Database Schema

Guides

​Overview

​Error Code Structure

​Common Error Classes

​Critical Errors in Payroll Calculation

​SQLSTATE 22008 - Datetime Field Overflow

​SQLSTATE 23502 - Not Null Violation

​SQLSTATE 23503 - Foreign Key Violation

​SQLSTATE 23505 - Unique Violation

​SQLSTATE 22001 - String Data Right Truncation

​SQLSTATE P0001 - Raise Exception

​Data Exception Errors (Class 22)

​Integrity Constraint Errors (Class 23)

​PL/pgSQL Errors (Class P0)

​Syntax and Access Errors (Class 42)

​System Resource Errors (Class 53)

​Error Handling Best Practices

​Querying Error Codes

​Next Steps

Payroll Calculation

Employee Management

Build docs developers (and LLMs) love

Overview

Error Code Structure

Common Error Classes

Critical Errors in Payroll Calculation

SQLSTATE 22008 - Datetime Field Overflow

SQLSTATE 23502 - Not Null Violation

SQLSTATE 23503 - Foreign Key Violation

SQLSTATE 23505 - Unique Violation

SQLSTATE 22001 - String Data Right Truncation

SQLSTATE P0001 - Raise Exception

Data Exception Errors (Class 22)

Integrity Constraint Errors (Class 23)

PL/pgSQL Errors (Class P0)

Syntax and Access Errors (Class 42)

System Resource Errors (Class 53)

Error Handling Best Practices

Querying Error Codes

Next Steps