postgresql substr: guía práctica y ejemplos
Hay operaciones en bases de datos que aparentan ser triviales hasta que fallan en producción. Extraer partes de cadenas es una de ellas. Conocer bien postgresql substr permite resolver transformaciones comunes sin matar el rendimiento ni complicar las consultas. A continuación se describe con claridad la sintaxis, casos reales, errores habituales y soluciones prácticas para aplicar en proyectos reales.
Qué hace substr en PostgreSQL
La función substr (sinónimo de substring) devuelve una porción de texto a partir de una posición inicial y, opcionalmente, una longitud. Los índices están basados en 1: el primer carácter es la posición 1. La función es útil para limpiar, normalizar y extraer patrones simples antes de pasar a transformaciones más complejas.
Sintaxis y variantes
Existen dos formas habituales de usarla:
Forma funcional: substr(string, start, length)
Forma SQL: substring(string from start for length)
Ejemplos:
SELECT substr(‘postgres’, 1, 4); — devuelve post
SELECT substring(‘postgres’ from 5); — devuelve rest (desde posición 5 hasta el final)
Comportamiento con valores atípicos
Si la posición de inicio es mayor que la longitud de la cadena, el resultado es una cadena vacía. Si el tercer argumento (length) se omite, la extracción va hasta el final. Nulls se propagan: substr(NULL, 1, 3) devuelve NULL.
Casos de uso concretos
Estos mini-casos muestran usos cotidianos y cómo plantearlos sin complicaciones.
Extraer dominio de un correo
Si la columna email contiene ‘usuario@dominio.com’, una manera simple es:
SELECT substr(email, position(‘@’ in email) + 1) AS dominio FROM usuarios;
Este enfoque evita regex cuando el formato es confiable y mejora la legibilidad.
Obtener prefijo o sufijo
Para un SKU donde los tres primeros caracteres indican categoría:
SELECT substr(sku, 1, 3) AS categoria FROM inventario;
Para los últimos cuatro caracteres (sufijo):
SELECT substr(sku, char_length(sku) – 3, 4) FROM inventario;
Expresiones regulares y substr
PostgreSQL permite usar versiones con patrón. Cuando la estructura no es fija, es más robusto recurrir a regex en lugar de índices locales.
Capturas simples con regex
La forma es substring(string from ‘pattern’). Si el patrón tiene grupos (), devuelve la primera coincidencia capturada.
Ejemplos:
SELECT substring(‘abc123def’ FROM ‘([0-9]+)’); — devuelve 123
SELECT substring(‘ID: 9876; COD’ FROM ‘ID: ([0-9]+)’); — útil para logs o campos semi-estructurados
Regex versus split_part
Cuando el separador es consistente, split_part suele ser más claro y rápido:
SELECT split_part(email, ‘@’, 2) AS dominio FROM usuarios;
Usar regex aporta flexibilidad para patrones variables, pero puede ser más costoso.
Rendimiento y buenas prácticas
Usar substr en consultas no es intrínsecamente lento, pero hay trampas que afectan índices y planes.
- Evitar funciones en columnas filtradas: WHERE substr(col,1,5) = ‘X’ impedirá el uso de un índice normal sobre col.
- Crear expression indexes si se usan extracciones repetidas: CREATE INDEX ON tabla (substr(col,1,5));
- Para búsquedas sobre patrones, considerar pg_trgm y un índice GIN/GiST: permite LIKE ‘%pat%’ eficiente.
- Preferir split_part cuando el separador es consistente; suele ser más rápido y expresivo.
- Cuando se procesan muchas filas, mover la transformación a ETL o columnas generadas evita calcular substr en cada SELECT.
Errores comunes y cómo evitarlos
Al trabajar con substr aparecen errores frecuentes que consumen tiempo en producción. Aquí se listan con soluciones prácticas.
Problema: pérdida de índices
Filtro como WHERE substr(nombre,1,3) = ‘abc’ no utilizará un índice b-tree sobre nombre. Solución: crear un índice de expresión o un campo materializado con la subcadena.
Problema: caracteres multibyte
length(text) devuelve número de caracteres, octet_length devuelve bytes. Para cadenas UTF-8 con acentos o caracteres no latinos, usar char_length o length para contar caracteres y evitar recortes en mitad de un carácter.
Comparación con otras bases de datos
La mayoría de SGBD usan índices basados en 1. Ejemplos:
- MySQL: SUBSTRING(str,pos,len) — sintaxis muy similar a PostgreSQL.
- SQL Server: SUBSTRING(expression,start,length) — comportamiento comparable.
- PostgreSQL destaca por la integración con regex en substring(string FROM pattern), lo que ofrece una ventaja cuando el contenido es variable.
En resumen, la API es familiar pero la potencia real viene de combinar substr con índices apropiados y funciones nativas como split_part o regex cuando conviene.
Conclusión práctica y accionable
Para aplicar postgresql substr en proyectos reales, seguir estos pasos:
- Identificar si la extracción es fija o basada en patrón. Si es fija, usar substr o split_part.
- Si la extracción aparece en filtros frecuentes, crear un índice de expresión o una columna generada para evitar escaneos completos.
- Para datos multilenguaje, medir con char_length y evitar cortar bytes con octet_length.
- Usar regex con moderación: poderosa, pero más costosa. Preferir split_part cuando el delimitador es consistente.
Aplicando estas reglas se obtienen consultas más claras y con mejor respuesta en entornos productivos. No existen fórmulas mágicas, pero sí decisiones prácticas: elegir la herramienta adecuada (substr, split_part, regex), respaldarla con índices y mover transformaciones pesadas fuera del path de consulta.
