Menu
CoddyTech

Hoja de Trucos de PostgreSQL

Última actualización

Comandos de psql

Los metacomandos se ejecutan dentro de psql (el cliente de terminal de PostgreSQL), no en el servidor. Empiezan con una barra invertida y no necesitan punto y coma.

Qué haceComando
Listar bases de datos\l
Conectarse a otra base de datos\c shop
Listar tablas\dt
Listar tablas de todos los esquemas\dt *.*
Describir una tabla (columnas, índices, claves)\d users
Describir con almacenamiento y comentarios\d+ users
Listar esquemas\dn
Listar vistas\dv
Listar índices\di
Listar funciones\df
Listar roles (usuarios)\du
Mostrar la conexión actual\conninfo
Ejecutar SQL desde un archivo\i setup.sql
Activar o desactivar el tiempo de las consultas\timing
Salida expandida (una columna por línea)\x
Editar la última consulta en tu editor\e
Ayuda sobre la sintaxis SQL\h CREATE TABLE
Listar todos los metacomandos\?
Salir de psql\q

Conectarse desde la terminal

Qué haceComando
Conectarse como un usuario a una base de datospsql -U postgres -d shop
Conectarse a un host y puerto remotospsql -h db.example.com -p 5432 -U app -d shop
Conectarse con una URL de conexiónpsql postgresql://app:secret@localhost:5432/shop
Ejecutar una consulta y salirpsql -d shop -c "SELECT count(*) FROM users;"
Ejecutar un archivo SQLpsql -d shop -f setup.sql
Crear una base de datos desde la shellcreatedb shop
Eliminar una base de datos desde la shelldropdb shop

Bases de datos y esquemas

Qué haceSQL
Crear una base de datosCREATE DATABASE shop;
Eliminar una base de datosDROP DATABASE shop;
Mostrar la base de datos actualSELECT current_database();
Crear un esquemaCREATE SCHEMA sales;
Mostrar el orden de búsqueda de esquemasSHOW search_path;
Buscar primero en un esquemaSET search_path TO sales, public;
Mostrar la versión del servidorSELECT version();

Tablas y tipos de datos

Qué haceSQL
Crear una tabla con una clave de identidadCREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now());
Añadir una columnaALTER TABLE users ADD COLUMN phone text;
Renombrar una columnaALTER TABLE users RENAME COLUMN country TO country_code;
Cambiar el tipo de una columnaALTER TABLE users ALTER COLUMN age TYPE smallint;
Definir un valor por defectoALTER TABLE users ALTER COLUMN active SET DEFAULT false;
Eliminar una columnaALTER TABLE users DROP COLUMN tags;
Vaciar una tabla y reiniciar su identidadTRUNCATE orders, users RESTART IDENTITY;
Eliminar una tabla si existeDROP TABLE IF EXISTS products;
Decimal exacto para dineronumeric(10,2)
Fecha y hora con zona horariatimestamptz
UUID aleatorioSELECT gen_random_uuid();
Tipo enumeradoCREATE TYPE mood AS ENUM ('happy', 'sad');

Restricciones y claves

Qué haceSQL
Añadir una clave primariaALTER TABLE logs ADD PRIMARY KEY (id);
Clave foránea que elimina las filas hijasCREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE);
Añadir una restricción CHECKALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0);
Único donde los NULL cuentan como iguales (15+)CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT);
Hacer obligatoria una columnaALTER TABLE users ALTER COLUMN country SET NOT NULL;
Eliminar una restricciónALTER TABLE users DROP CONSTRAINT users_email_key;

Consultas (extras de PostgreSQL)

SELECT, WHERE y JOIN estándar están en la hoja de trucos de SQL; estas son las formas propias de PostgreSQL.

Qué haceSQL
Coincidencia sin distinguir mayúsculasSELECT * FROM users WHERE name ILIKE 'a%';
Coincidencia con expresión regularSELECT * FROM users WHERE email ~ '@example\.com$';
Primera fila de cada grupoSELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;
Límite y desplazamientoSELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
Límite de filas estándarSELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
Conversión con ::SELECT '42'::integer + 1;
Valor alternativo para NULLSELECT name, COALESCE(email, 'none') FROM users;
Comparar tratando los NULL como igualesSELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com';
Generar una serie de númerosSELECT * FROM generate_series(1, 5);
Agregar en una cadenaSELECT string_agg(name, ', ' ORDER BY name) FROM users;
Agregado condicionalSELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders;

Insertar, actualizar, eliminar y upsert

Qué haceSQL
Insertar y recuperar el nuevo idINSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id;
Insertar varias filasINSERT INTO users (name) VALUES ('Eli'), ('Femi');
Upsert (insertar o actualizar)INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
Insertar ignorando duplicadosINSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING;
Actualizar y ver las filas modificadasUPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary;
Actualizar desde otra tablaUPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN';
Eliminar y ver lo eliminadoDELETE FROM orders WHERE status = 'refunded' RETURNING id;
Eliminar usando otra tablaDELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false;
Copiar filas a una tabla nuevaCREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid';

JSONB

Qué haceSQL
Obtener un campo como JSONSELECT profile -> 'plan' FROM users;
Obtener un campo como textoSELECT profile ->> 'plan' FROM users;
Obtener un campo anidado como textoSELECT profile #>> '{address,city}' FROM users;
Filas cuyo JSON contiene un valorSELECT * FROM users WHERE profile @> '{"plan": "pro"}';
Filas que tienen una claveSELECT * FROM users WHERE profile ? 'city';
Asignar una claveUPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2;
Combinar objetosUPDATE users SET profile = profile || '{"beta": true}';
Quitar una claveUPDATE users SET profile = profile - 'city';
Construir JSON a partir de columnasSELECT jsonb_build_object('id', id, 'name', name) FROM users;
Filas como un array JSONSELECT jsonb_agg(users) FROM users;
Índice para búsquedas con @> y ?CREATE INDEX users_profile_idx ON users USING gin (profile);

Arrays

Qué haceSQL
Literal de arraySELECT ARRAY[1, 2, 3];
Filas donde el array tiene un valorSELECT * FROM users WHERE 'sql' = ANY(tags);
Filas donde el array contiene todos estosSELECT * FROM users WHERE tags @> ARRAY['sql', 'python'];
Una fila por elementoSELECT name, unnest(tags) FROM users;
Añadir un elemento al finalUPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2;
Longitud del arraySELECT name, cardinality(tags) FROM users;

Fechas y horas

Qué haceSQL
Ahora, con zona horariaSELECT now();
HoySELECT current_date;
Sumar un intervaloSELECT current_date + INTERVAL '7 days';
Filas de los últimos 30 díasSELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days';
Truncar al mesSELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
Extraer una parteSELECT EXTRACT(YEAR FROM hired_on) FROM employees;
Diferencia entre fechas (días)SELECT DATE '2026-12-25' - DATE '2026-11-17';
Edad entre fechasSELECT name, age(current_date, hired_on) FROM employees;
Formatear una fecha como textoSELECT to_char(hired_on, 'DD Mon YYYY') FROM employees;
Convertir texto en fechaSELECT to_date('17/11/2026', 'DD/MM/YYYY');
Convertir a una zona horariaSELECT now() AT TIME ZONE 'Asia/Kolkata';

Funciones de texto

Qué haceSQL
Concatenar (un NULL da NULL)SELECT name || ' <' || email || '>' FROM users;
Concatenar omitiendo los NULLSELECT concat_ws(' ', name, email) FROM users;
Mayúsculas y minúsculasSELECT upper(name), lower(name) FROM users;
LongitudSELECT name, length(name) FROM users;
Parte de una cadenaSELECT substring(name FROM 1 FOR 2) FROM users;
ReemplazarSELECT replace(email, '@example.com', '') FROM users;
Dividir y tomar una parteSELECT split_part(email, '@', 2) FROM users;
Reemplazo con expresión regularSELECT regexp_replace('a1b22c', '[0-9]+', '', 'g');
Quitar espaciosSELECT trim(' hi ');

CTEs y funciones de ventana

Qué haceSQL
Nombrar una subconsulta con WITHWITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50;
CTE recursiva (contar hasta 5)WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n;
Numerar filas por grupoSELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Ranking con empatesSELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
Total acumuladoSELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders;
Valor de la fila anteriorSELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees;
Unir cada fila con su propia subconsultaSELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o;

Índices

Qué haceSQL
Índice B-tree (el predeterminado)CREATE INDEX orders_user_idx ON orders (user_id);
Índice únicoCREATE UNIQUE INDEX employees_name_idx ON employees (name);
Índice multicolumnaCREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
Índice parcialCREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid';
Índice de expresiónCREATE INDEX users_lower_email_idx ON users (lower(email));
Índice de cobertura (escaneos solo de índice)CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount);
Crear sin bloquear escriturasCREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
Eliminar un índiceDROP INDEX IF EXISTS orders_user_idx;
Ver el planEXPLAIN SELECT * FROM orders WHERE user_id = 1;
Ver el plan con tiempos realesEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Transacciones y bloqueos

Qué haceSQL
Empezar y luego confirmar o deshacerBEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK;
Deshacer parte de una transacciónBEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT;
Aislamiento más estrictoBEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT;
Bloquear las filas que leesBEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT;
Cola de trabajos: saltar filas bloqueadasBEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT;

Usuarios, roles y privilegios

Qué haceSQL
Crear un rol con inicio de sesiónCREATE ROLE app LOGIN PASSWORD 'change-me';
Acceso de solo lectura a una tablaGRANT SELECT ON users TO app;
Acceso de lectura a todas las tablas de un esquemaGRANT SELECT ON ALL TABLES IN SCHEMA public TO app;
Retirar un privilegioREVOKE SELECT ON users FROM app;
Cambiar una contraseñaALTER ROLE app PASSWORD 'new-secret';
Mostrar el usuario actualSELECT current_user;

Mantenimiento y monitorización

Qué haceSQL
Consultas en ejecuciónSELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
Cancelar una consultaSELECT pg_cancel_backend(12345);
Terminar una conexiónSELECT pg_terminate_backend(12345);
Tamaño de una tabla con sus índicesSELECT pg_size_pretty(pg_total_relation_size('orders'));
Tamaño de la base de datosSELECT pg_size_pretty(pg_database_size(current_database()));
Actualizar las estadísticas del planificadorANALYZE orders;
Recuperar filas muertasVACUUM orders;
Reescribir una tabla a su tamaño mínimo (la bloquea)VACUUM FULL orders;
Mostrar un parámetroSHOW work_mem;
Cambiar un parámetro para esta sesiónSET work_mem = '64MB';

Copia de seguridad y restauración

Comandos de shell de las herramientas cliente de PostgreSQL.

Qué haceComando
Volcar una base de datos a un archivo SQLpg_dump -U postgres shop > shop.sql
Volcar en el formato personalizadopg_dump -Fc -U postgres shop > shop.dump
Volcar una tablapg_dump -t orders shop > orders.sql
Restaurar un volcado SQLpsql -d shop -f shop.sql
Restaurar un volcado en formato personalizadopg_restore -d shop shop.dump
Volcar todas las bases de datos y rolespg_dumpall > cluster.sql
Exportar una consulta a CSV (desde psql)\copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER
Importar un CSV (desde psql)\copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER

Cada comando de psql y cada sentencia de PostgreSQL que necesitas, en tablas listas para copiar y pegar: primero la conexión y los comandos \d, luego tablas, upserts, JSONB, fechas, funciones de ventana, índices, roles, copias de seguridad y mantenimiento.

El SQL de esta página usa la sintaxis de PostgreSQL 17, y la mayor parte también funciona en versiones anteriores. Las sentencias SQL se comprobaron en PostgreSQL 17; para probar una, pégala en el playground de PostgreSQL, que incluye tablas de ejemplo.

Preguntas frecuentes sobre la hoja de trucos de PostgreSQL

¿Qué son los comandos de psql?
psql es el cliente de terminal de PostgreSQL. Además de SQL, entiende metacomandos que empiezan con una barra invertida: \l lista las bases de datos, \c se conecta a una, \dt lista las tablas, \d users describe una tabla y \q sale. Se ejecutan en el propio psql, así que no necesitan punto y coma y no funcionan en otros clientes.
¿Cómo listo todas las tablas en PostgreSQL?
En psql, escribe \dt (o \dt *.* para todos los esquemas). En SQL, desde cualquier cliente: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
¿El SQL de PostgreSQL es diferente del de MySQL?
El núcleo (SELECT, JOIN, GROUP BY) es el mismo. PostgreSQL añade ILIKE, las conversiones con ::, RETURNING, DISTINCT ON, los upserts con ON CONFLICT, los arrays y JSONB, y usa columnas de identidad donde MySQL usa AUTO_INCREMENT.
¿Cuál es la diferencia entre JSON y JSONB?
json guarda el texto tal como se escribió; jsonb guarda una forma binaria ya analizada, elimina las claves duplicadas y los espacios en blanco, y admite índices (GIN) y operadores como @> y ?. Usa jsonb salvo que necesites el texto original exacto.
¿Puedo practicar estas consultas en línea?
Sí. El playground de PostgreSQL ejecuta cualquier consulta en una base de datos PostgreSQL 17 real en tu navegador, con tablas de ejemplo que puedes editar. Las sentencias que necesitan privilegios de servidor, como CREATE DATABASE o CREATE ROLE, requieren tu propio PostgreSQL.
¿Esta hoja de trucos de PostgreSQL es gratis?

Sí. Es gratis para leer, copiar e imprimir, sin registro.

Ilustración de los lenguajes de programación de Coddy

Aprende PostgreSQL con Coddy

Comenzar