SQLearnAcademy

Referencia de funciones de PostgreSQL

Lo esencial de cada función: qué hace, cómo se llama, ejemplos que puedes modificar y ejecutar aquí mismo, y en qué fijarte para no tropezar.

Estas son las funciones principales, suficientes para la mayoría de las consultas. Todas las demás están en la documentación oficial de PostgreSQL(se abre en una pestaña nueva).

Condicionales

COALESCE

Devuelve el primer valor de la lista que no es NULL.

COALESCE(<valor1>, <valor2>, …)NULLIF

Devuelve NULL si dos valores son iguales y, si no, el primer valor.

NULLIF(<valor>, <comparar con>)GREATEST

Devuelve el mayor de una lista de valores.

GREATEST(<valor1>, <valor2>, …)LEAST

Devuelve el menor de una lista de valores.

LEAST(<valor1>, <valor2>, …)CASE

Devuelve valores distintos según las condiciones: el «si… entonces… si no» de SQL.

CASE WHEN <condición> THEN <resultado> … ELSE <si no> END

Números

ROUND

Redondea un número a un entero o al número de decimales que necesites.

ROUND(<número>)CEIL

Redondea un número hacia arriba: al entero más cercano que no sea menor.

CEIL(<número>)FLOOR

Redondea un número hacia abajo: al entero más cercano que no sea mayor.

FLOOR(<número>)TRUNC

Quita la parte decimal de un número sin redondear.

TRUNC(<número>)ABS

Devuelve el valor absoluto de un número: su valor sin signo.

ABS(<número>)MOD

Devuelve el resto de dividir un número entre otro.

MOD(<dividendo>, <divisor>)POWER

Eleva un número a una potencia.

POWER(<número>, <exponente>)SQRT

Calcula la raíz cuadrada de un número.

SQRT(<número>)WIDTH_BUCKET

Indica en cuál de varios rangos iguales cae un número: 1, 2, 3…

WIDTH_BUCKET(<valor>, <desde>, <hasta>, <número de cubetas>)RANDOM

Devuelve un número decimal aleatorio de 0 (incluido) a 1 (excluido).

RANDOM()

Cadenas

LENGTH

Cuenta cuántos caracteres tiene una cadena.

LENGTH(<texto>)LOWER

Pasa un texto a minúsculas.

LOWER(<texto>)UPPER

Pasa un texto a mayúsculas.

UPPER(<texto>)INITCAP

Pone en mayúscula la primera letra de cada palabra y el resto en minúscula.

INITCAP(<texto>)TRIM

Quita los espacios (u otros caracteres) del principio y del final de una cadena.

TRIM(<texto>)SUBSTRING

Extrae una parte de una cadena: desde la posición y con la longitud que necesites.

SUBSTRING(<texto> FROM <inicio>)LEFT

Toma el número indicado de caracteres del principio de una cadena.

LEFT(<texto>, <cantidad>)RIGHT

Toma el número indicado de caracteres del final de una cadena.

RIGHT(<texto>, <cantidad>)POSITION

Encuentra en qué carácter empieza una subcadena dentro de una cadena.

POSITION(<subcadena> IN <texto>)STARTS_WITH

Comprueba si una cadena empieza por el prefijo indicado.

STARTS_WITH(<texto>, <prefijo>)REPLACE

Sustituye en una cadena todas las apariciones de un texto por otro.

REPLACE(<texto>, <buscar>, <reemplazo>)TRANSLATE

Sustituye caracteres sueltos por otros, cada uno por el suyo, en una sola pasada.

TRANSLATE(<texto>, <caracteres>, <reemplazos>)REGEXP_LIKE

Comprueba si una cadena encaja con una expresión regular.

REGEXP_LIKE(<texto>, '<patrón>')REGEXP_MATCHES

Extrae de una cadena las partes que encajan con una expresión regular.

REGEXP_MATCHES(<texto>, '<patrón>')REGEXP_SUBSTR

Devuelve el primer fragmento de una cadena que encaja con una expresión regular, como texto normal.

REGEXP_SUBSTR(<texto>, '<patrón>')REGEXP_REPLACE

Sustituye las partes de una cadena que encajan con una expresión regular.

REGEXP_REPLACE(<texto>, '<patrón>', <reemplazo>)SPLIT_PART

Divide una cadena por un separador y devuelve la parte con el número que necesites.

SPLIT_PART(<texto>, <separador>, <número de parte>)CONCAT

Une varios valores en una sola cadena, saltándose los NULL.

CONCAT(<valor1>, <valor2>, …)FORMAT

Construye una cadena a partir de una plantilla, poniendo valores en lugar de %s.

FORMAT('<plantilla>', <valor1>, <valor2>, …)LPAD

Rellena una cadena por la izquierda hasta la longitud que necesites.

LPAD(<texto>, <longitud>)RPAD

Rellena una cadena por la derecha hasta la longitud que necesites.

RPAD(<texto>, <longitud>)REPEAT

Repite una cadena el número de veces indicado.

REPEAT(<texto>, <veces>)REVERSE

Da la vuelta a una cadena.

REVERSE(<texto>)MD5

Calcula el hash MD5 de una cadena: 32 caracteres hexadecimales.

MD5(<texto>)

Fechas y horas

DATE_TRUNC

Recorta una fecha y hora al inicio del año, mes, semana, día u hora.

DATE_TRUNC('<unidad>', <momento>)DATE_BIN

Redondea un momento hacia abajo al inicio de un intervalo de cualquier longitud: 15 minutos, 6 horas, 10 días.

DATE_BIN(INTERVAL '<paso>', <momento>, <origen>)EXTRACT

Saca una parte concreta de una fecha u hora: el año, el mes, el día, la hora.

EXTRACT(<campo> FROM <fecha u hora>)DATE_PART

Saca una parte concreta de una fecha u hora: lo mismo que EXTRACT, pero como función normal.

DATE_PART('<campo>', <fecha u hora>)NOW

Devuelve la fecha y la hora actuales: el momento en que empezó la transacción.

NOW()CURRENT_DATE

Devuelve la fecha de hoy, sin la hora.

CURRENT_DATEAGE

Calcula cuánto tiempo ha pasado entre dos momentos, en años, meses y días.

AGE(<momento posterior>, <momento anterior>)MAKE_DATE

Construye una fecha a partir de números sueltos: el año, el mes y el día.

MAKE_DATE(<año>, <mes>, <día>)MAKE_INTERVAL

Construye un intervalo a partir de números sueltos: años, meses, días, horas.

MAKE_INTERVAL(<unidad> => <cantidad>, …)TO_CHAR

Convierte una fecha, una hora o un número en texto con el formato que necesites.

TO_CHAR(<valor>, '<formato>')TO_DATE

Convierte un texto en fecha según el formato indicado.

TO_DATE(<texto>, '<formato>')

Tipos

CAST

Convierte un valor de un tipo a otro: texto a número, texto a fecha, número a texto.

CAST(<valor> AS <tipo>)TO_NUMBER

Convierte en número un texto escrito «a lo humano»: con comas o con símbolo de moneda.

TO_NUMBER(<texto>, '<formato>')GEN_RANDOM_UUID

Genera un UUID aleatorio: un identificador único como 3f2c9a1e-….

GEN_RANDOM_UUID()

Arrays y series

STRING_TO_ARRAY

Divide una cadena por un separador en un array de partes.

STRING_TO_ARRAY(<texto>, <separador>)ARRAY_TO_STRING

Une los elementos de un array en una sola cadena con un separador.

ARRAY_TO_STRING(<array>, <separador>)UNNEST

Despliega un array en filas: una fila por elemento.

UNNEST(<array>)CARDINALITY

Cuenta cuántos elementos tiene un array.

CARDINALITY(<array>)ARRAY_LENGTH

Devuelve la longitud de un array en la dimensión indicada.

ARRAY_LENGTH(<array>, <dimensión>)ARRAY_POSITION

Encuentra el número del primer elemento del array con el valor indicado.

ARRAY_POSITION(<array>, <valor>)ARRAY_APPEND

Devuelve un array con un elemento añadido al final.

ARRAY_APPEND(<array>, <elemento>)ARRAY_REMOVE

Devuelve un array sin ninguno de los elementos con el valor indicado.

ARRAY_REMOVE(<array>, <valor>)GENERATE_SERIES

Crea filas con una secuencia de números o de momentos.

GENERATE_SERIES(<inicio>, <fin>)

JSON

TO_JSONB

Convierte un valor —un número, un texto, un array o una fila entera de la tabla— en JSON.

TO_JSONB(<valor>)JSONB_BUILD_OBJECT

Construye un objeto JSON a partir de pares clave–valor.

JSONB_BUILD_OBJECT(<clave1>, <valor1>, <clave2>, <valor2>, …)JSONB_AGG

Reúne valores de muchas filas en un solo array JSON.

JSONB_AGG(<expresión>)JSONB_SET

Devuelve una copia del JSON con un valor nuevo en la ruta indicada.

JSONB_SET(<json>, '{<ruta>}', <nuevo valor>)JSONB_EACH

Despliega un objeto JSON en filas: una fila por cada par clave–valor.

JSONB_EACH(<objeto json>)JSONB_OBJECT_KEYS

Devuelve las claves de un objeto JSON: una fila por clave.

JSONB_OBJECT_KEYS(<objeto json>)JSONB_ARRAY_ELEMENTS

Despliega un array JSON en filas: una fila por elemento.

JSONB_ARRAY_ELEMENTS(<array json>)JSONB_PATH_QUERY

Saca valores de un JSON con una expresión SQL/JSON path, también con condiciones.

JSONB_PATH_QUERY(<json>, '<ruta>')

Búsqueda de texto completo

TO_TSVECTOR

Prepara un texto para la búsqueda de texto completo: lo divide en palabras y las reduce a su raíz.

TO_TSVECTOR('<configuración de idioma>', <texto>)TO_TSQUERY

Construye una consulta de búsqueda de texto completo: palabras con condiciones «y», «o» y «no».

TO_TSQUERY('<configuración de idioma>', '<consulta>')

De agregación

COUNT

Cuenta filas o valores que no son NULL.

COUNT(*)SUM

Suma los valores de muchas filas y devuelve el total.

SUM(<expresión>)AVG

Calcula la media aritmética de los valores de muchas filas.

AVG(<expresión>)MIN

Devuelve el valor más pequeño entre las filas.

MIN(<expresión>)MAX

Devuelve el valor más grande entre las filas.

MAX(<expresión>)PERCENTILE_CONT

Encuentra la mediana u otro percentil: el valor por debajo del cual queda una proporción dada de los datos.

PERCENTILE_CONT(<fracción>) WITHIN GROUP (ORDER BY <expresión>)PERCENTILE_DISC

Encuentra un percentil entre los valores existentes: una mediana que está seguro en los datos.

PERCENTILE_DISC(<fracción>) WITHIN GROUP (ORDER BY <expresión>)BOOL_AND

Devuelve true si una condición se cumple en todas las filas.

BOOL_AND(<condición>)BOOL_OR

Devuelve true si una condición se cumple al menos en una fila.

BOOL_OR(<condición>)STRING_AGG

Une valores de muchas filas en un solo texto con un separador.

STRING_AGG(<expresión>, <separador>)ARRAY_AGG

Reúne valores de muchas filas en un solo array.

ARRAY_AGG(<expresión>)GROUPING

Indica si una fila es un subtotal de ROLLUP o CUBE para una columna: 1 — subtotal, 0 — grupo normal.

GROUPING(<columna>)

De ventana

ROW_NUMBER

Numera las filas 1, 2, 3… en un orden dado: en todo el resultado o dentro de cada grupo.

ROW_NUMBER() OVER (ORDER BY <criterio de orden>)RANK

Asigna puestos teniendo en cuenta los empates: los valores iguales comparten puesto y el siguiente se salta.

RANK() OVER (ORDER BY <criterio de orden>)DENSE_RANK

Asigna puestos teniendo en cuenta los empates, pero sin huecos: 1, 2, 2, 3.

DENSE_RANK() OVER (ORDER BY <criterio de orden>)PERCENT_RANK

Muestra la posición relativa de una fila de 0 a 1: qué proporción de las demás filas queda por delante.

PERCENT_RANK() OVER (ORDER BY <criterio de orden>)CUME_DIST

Muestra la proporción de filas que quedan por delante de la actual o empatan con ella, de 0 a 1.

CUME_DIST() OVER (ORDER BY <criterio de orden>)NTILE

Divide las filas en el número de grupos indicado, de tamaño parecido, y numera los grupos.

NTILE(<número de grupos>) OVER (ORDER BY <criterio de orden>)LAG

Toma un valor de la fila anterior para compararlo con el de la actual.

LAG(<expresión>) OVER (ORDER BY <criterio de orden>)LEAD

Toma un valor de la fila siguiente para compararlo con el de la actual.

LEAD(<expresión>) OVER (ORDER BY <criterio de orden>)FIRST_VALUE

Toma el valor de la primera fila de la ventana.

FIRST_VALUE(<expresión>) OVER (ORDER BY <criterio de orden>)LAST_VALUE

Toma el valor de la última fila de la ventana, pero solo con el marco correcto.

LAST_VALUE(<expresión>) OVER ( ORDER BY <criterio de orden> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )NTH_VALUE

Toma el valor de la fila N de la ventana: la segunda, la tercera…

NTH_VALUE(<expresión>, <n>) OVER ( ORDER BY <criterio de orden> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )