PSQL es el entorno de terminal para el manejo del motor de Base de Datos PostgreSQL.

Conectarse a una base de datos

Para iniciarla podemos hacer con el siguiente comando:

$ psql -U username -W -h iphost basename

Parámetros:

• -U es el usuario de la base

• -W mostrará el prompt de solicitud de password

• -h IP del servidor de la base de datos en caso nos conectemos remotamente sino bastaría con poner localhost

• basename es el nombre de la base de datos

Luego ya estaremos dentro de postgres y podremos hacer consultas:

basename=# SELECT * FROM tabla;

(no olvidemos el ; al final de cada query)

Comandos de navegación y consulta de información

Mostrar las bases de datos

basename=# \l

Mostrar Tablas

Mostrar las tablas de la base de datos actual

basename=# \d

Muestra los atributos o columnas de una tabla

basename=# \d tabla

Similar al anterior pero con mas detalles, a mi gusto aporta poco.

basename=# \d+ tabla

Listar los esquemas de la base de datos actual

basename=# \dn

Listar las funciones disponibles de la base de datos actual

basename=# \df

Listar las vistas de la base de datos actual

basename=# \dv

Listar los usuarios y sus roles de la base de datos actual

basename=# \du

Listar Tablas de la base de datos

Mostrar la lista de las tablas de la base de datos que tengamos seleccionada:

basename=# \dt

Cambiar de base de datos

Seleccionar una base de datos o cambiar de base de datos:

basename=# \c basename2

Cambiar de Usuario

basename=# \c basename usuario2

Otros comandos

Ver la ayuda con respecto a la sintaxis de nuestras consultas SQL:

basename=# \h INSERT

Al utilizar este comando nos mostrará como debemos armar nuestro "insert", así como parametros y el orden correspondiente.

Hacer que una consulta se ejecute cada ciertos segundos:

Primero ejecutamos nuestra consulta:

basename=# select count(*) from books;

Segundo ejecutamos nuestro comando \watch + los segundos

basename=# \watch 1

Editar la consulta que tengamos en el buffer con un editor mas amigable como nano:

basename=# \e

Al ejecutarlo por primera vez, este comando te mostrará un menu donde puedes elegir el editor.

La segunda vez que ejecutemos este comando veremos que nos lo habré automáticamente con el editor selecionado (nano) y luego de hacer los cambios y guardemos (ctrl + o) veremos que se ejecutará la consulta.

Ademas con el comando \p podemos ver lo que teníamos en el buffer

Si queremos guardar lo que tengamos en buffer en un archivo podemos utilizar el comando \w de la siguiente manera:

basename=# \w /tmp/test.sql

OJO si utilizamos un archivo que ya este creado previamente por nosotros tenemos que darle permisos de escritura (chmod 777 archivo), por ejemplo si tenemos nuestro archivo en /home/miusuario/mis.sql tendríamos que darle permisos de escritura primero antes de ejecutar nuestro comando desde la consola interactiva.

Ejecutar los consultas (querys) que tengamos en un archivo externo:

basename=# \i /tmp/test.sql

Guarda los resultados de una consulta en un archivo:

Primero ejecutamos \o seguido del archivo

basename=# \o /tmp/out.sql

Segundo ejecutamos nuestra consulta:

basename=# select * from books;

no nos muestra los resultados de la consulta en la consola sino que tendremos que ver nuestro archivo /tmp/out.sql, lo podemos hacer abriendo otra consola y ejecutar el siguiente comando:

basename=# cat /tmp/out.sql

Ahora un concejo importante después de utilizar este comando todas nuestras consultas mandaran los resultados al archivo (una tras otra), para dejar de mandar los resultados al archivo solamente ejecutamos el comando\o sin definir el archivo:

basename=# \o

Y veremos como de nuevo se nos muestran los resultados de nuestras consultas en la consola.

Mostrar la codificación que tiene activa nuestra consola interactiva:

basename=# \encoding

Obtener la salida de una consulta en formato HTML:

basename=# \H

Sirve para activar y desactivar la salidas de nuestras consultas en formato HTML, lo que hace este comando es crear una tabla HTML con los resultados de nuestra consulta.

Para desactivar las vista en formato HTML solamente tenemos que escribir nuevamente \H.

Podemos usar este comando en conjunto con nuestro comando \o y escribir de una sola vez nuestro archivo .html.

Información de nuestra conexión activa

basename=# \conninfo

Ayuda de PSQL:

mostrará una lista de todos los comandos que podemos usar en la consola.

basename=# \?

Vaciar una tabla

en especifico o el famoso TRUNCATE que conocemos:

basename=# TRUNCATE TABLE table RESTART IDENTITY

Con este comando borramos el contenido de una tabla y reiniciamos su índice sino agregamos RESTART IDENTITY nuestros índices no serán reiniciados y seguirán según el ultimo registro.

Crear Esquema

CREATE SCHEMA schemaname;

Modificar Esquema

ALTER SCHEMA schemaname RENAME TO newname

ALTER SCHEMA schemaname OWNER TO newowner

Eliminar Esquema

DROP SCHEMA schemaname CASCADE;

Crear una base de datos

basename=# CREATE DATABASE basename;

Borrar o eliminar una base de datos

basename=# DROP DATABASE basename;

Borrar o eliminar una tabla en especifico

basename=# DROP TABLE tablename;

Enviar resultados de una consulta a un archivo delimitado por |

basename=# COPY (SELECT * FROM tablename) TO '/home/tablename.csv' WITH DELIMITER '|';

Cabe mencionar que el archivo necesito permisos de escritura.

Uso de LIMIT y OFFSET

basename=# SELECT * FROM table LIMIT limit OFFSET offset;

Donde:

• • limit: es nuestro limite de registros a mostrar

  • offset: indica desde donde comenzaran a mostrarce los registros

Uso de comillas:

basename=# SELECT “column” FROM “table” WHERE “column” = 'value';

Generalmente podemos utilizar comillas dobles para nuestras columnas y comillas simples para nuestros valores, esto no es una regla pero a veces es necesario en casos especiales, tales como cuando ocupamos nombres reservados, por ejemplo:

basename=# SELECT to FROM table;

En este caso tenemos un campo llamado “to”, esto nos dará un error de sintaxis, por lo tanto tendremos que usar comillas dobles:

basename=# SELECT “to” FROM table;

debug y optimización

Activar / Desactivar el contador de tiempo por consulta

\timing

Salir del cliente psql:

basename=# \q

Comandos de inspección y ejecución

Volver a ejecutar el comando ejecutando justo antes

basename=# \g

Ver el historial de comandos ejecutados

basename=# \s

Si se quiere guardar la lista de comandos ejecutados en un archivo de texto plano

basename=# \s <nombre_archivo>

Ejecutar los comandos desde un archivo

basename=# \i <nombre_archivo>

Permite abrir un editor de texto plano, escribir comandos y ejecutar en lote. \e abre el editor de texto, escribir allí todos los comandos, luego guardar los cambios y cerrar, al cerrar se ejecutarán todos los comandos guardados.

basename=# \e

Equivalente al comando anterior pero permite editar también funciones en PostgreSQL

basename=# \ef

MAN

\copyright mostrar términos de uso y distribución de PostgreSQL

\g [ARCH] o ; enviar búfer de consulta al servidor (y resultados a archivo u |orden)

\gset [PREFIJO] ejecutar la consulta y almacenar los resultados en variables de psql

\h [NOMBRE] mostrar ayuda de sintaxis de órdenes SQL; use «*» para todas las órdenes

\q salir de psql

\watch [SEGS] ejecutar consulta cada SEGS segundos

Búfer de consulta

\e [ARCHIVO] [LÍNEA] editar el búfer de consulta (o archivo) con editor externo

\ef [NOMBRE-FUNCIÓN [LÍNEA]] editar una función con editor externo

\p mostrar el contenido del búfer de consulta

\r reiniciar (limpiar) el búfer de consulta

\s [ARCHIVO] mostrar historial de órdenes o guardarlo en archivo

\w ARCHIVO escribir búfer de consulta a archivo

Entrada/Salida

\copy ... ejecutar orden SQL COPY con flujo de datos al cliente

\echo [CADENA] escribir cadena a salida estándar

\i ARCHIVO ejecutar órdenes desde archivo

\ir ARCHIVO como \i, pero relativo a la ubicación del script actual

\o [ARCHIVO] enviar resultados de consultas a archivo u |orden

\qecho [CADENA] escribir cadena a salida de consultas (ver \o)

Informativo

(opciones: S = desplegar objectos de sistema, + = agregar más detalle)

\d[S+] listar tablas, vistas y secuencias

\d[S+] NOMBRE describir tabla, índice, secuencia o vista

\da[S] [PATRÓN] listar funciones de agregación

\db[+] [PATRÓN] listar tablespaces

\dc[S+] [PATRÓN] listar conversiones

\dC[+] [PATRÓN] listar conversiones de tipo (casts)

\dd[S] [PATRÓN] listar comentarios de objetos que no aparecen en otra parte

\ddp [PATRÓN] listar privilegios por omisión

\dD[S+] [PATRÓN] listar dominios

\det[+] [PATRÓN] listar tablas foráneas

\des[+] [PATRÓN] listar servidores foráneos

\deu[+] [PATRÓN] listar mapeos de usuario

\dew[+] [PATRÓN] listar conectores de datos externos

\df[antw][S+] [PATRÓN] listar funciones [sólo ag./normal/trigger/ventana]

\dF[+] [PATRÓN] listar configuraciones de búsqueda en texto

\dFd[+] [PATRÓN] listar diccionarios de búsqueda en texto

\dFp[+] [PATRÓN] listar analizadores (parsers) de búsq. en texto

\dFt[+] [PATRÓN] listar plantillas de búsqueda en texto

\dg[+] [PATRÓN] listar roles

\di[S+] [PATRÓN] listar índices

\dl listar objetos grandes, lo mismo que \lo_list

\dL[S+] [PATRÓN] listar lenguajes procedurales

\dm[S+] [PATRÓN] listar vistas materializadas

\dn[S+] [PATRÓN] listar esquemas

\do[S] [PATRÓN] listar operadores

\dO[S] [PATRÓN] listar ordenamientos (collations)

\dp [PATRÓN] listar privilegios de acceso a tablas, vistas y secuencias

\drds [PAT1 [PAT2]] listar parámetros de rol por base de datos

\ds[S+] [PATRÓN] listar secuencias

\dt[S+] [PATRÓN] listar tablas

\dT[S+] [PATRÓN] listar tipos de dato

\du[+] [PATRÓN] listar roles

\dv[S+] [PATRÓN] listar vistas

\dE[S+] [PATRÓN] listar tablas foráneas

\dx[+] [PATRÓN] listar extensiones

\dy [PATRÓN] listar disparadores por eventos

\l[+] [PATRÓN] listar bases de datos

\sf[+] FUNCIÓN mostrar la definición de una función

\z [PATRÓN] lo mismo que \dp

Formato

\a cambiar entre modo de salida alineado y sin alinear

\C [CADENA] definir título de tabla, o indefinir si es vacío

\f [CADENA] mostrar o definir separador de campos para modo de salida sin alinear

\H cambiar modo de salida HTML (actualmente desactivado)

\pset NOMBRE [VALOR] define opción de salida de tabla (NOMBRE := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})

\t [on|off] mostrar sólo filas (actualmente desactivado)

\T [CADENA] definir atributos HTML de <table>, o indefinir si es vacío

\x [on|off|auto] cambiar modo expandido (actualmente desactivado)

Conexiones

\c[onnect] [BASE-DE-DATOS|- USUARIO|- ANFITRIÓN|- PUERTO|- | conninfo]

conectar a una nueva base de datos (actual: «juaramir»)

\encoding [CODIFICACIÓN] mostrar o definir codificación del cliente

\password [USUARIO] cambiar la contraseña para un usuario en forma segura

\conninfo despliega la información sobre la conexión actual

Sistema Operativo

\cd [DIR] cambiar el directorio de trabajo actual

\setenv NOMBRE [VALOR] definir o indefinir variable de ambiente

\timing [on|off] mostrar tiempo de ejecución de órdenes (actualmente desactivado)

\! [ORDEN] ejecutar orden en intérprete de órdenes (shell), o iniciar intérprete interactivo

Variables

\prompt [TEXTO] NOMBRE preguntar al usuario el valor de la variable

\set [NOMBRE [VALOR]] definir variables internas, listar todas si no se dan parámetros

\unset NOMBRE indefinir (eliminar) variable interna

Objetos Grandes

\lo_export LOBOID ARCHIVO

\lo_import ARCHIVO [COMENTARIO]

\lo_list

\lo_unlink LOBOID operaciones con objetos grandes

Los datos aquí presentados son basados en los que puedes encontrar en http://viviryaprenderweb.com