EniDev

Guías sobre temas de bases de datos y programación

Project maintained by Hosted on GitHub Pages — Theme by mattgraham

CLIENTE DE LÍNEA DE COMANDOS

Postgre

Shows an illustrated sun in light mode and a moon with stars in dark mode.

CONTENIDO

INTRODUCCIÓN

Psql es una aplicación cliente que viene incluido en el paquete de PostgreSQL regular y está basado en la terminal. Nos permite establecer una conexión a un servidor de PostgreSQL donde debemos proporcionar las opciones de conexión como argumentos correctamente a través de la línea de comandos, establecida la conexión se abre una sesión interactiva para realizar consultas SQL y enviarlas a nuestro servidor de PostgreSQL y ver los resultados. Además, psql proporciona una serie de matacomandos y varias funciones similares a las de un shell para facilitar la escritura de scripts y la automatización de una amplia variedad de tareas.

Opciones en línea de comandos

Para conectarse al servidor, necesita saber el nombre de la base de datos de destino, el nombre de host, nombre de usuaurio, puerto del servidor, etc…, y con que nombre de usuario desea conectarse. Se puede informar a psql sobre esos parámetros a través de las opciones de la línea de comandos -d, -h, -U, -p respectivamente.

Ej: conectarse a postgres con el usuario postgres, al host localhost, y a la base de datos postgres

psql -h localhost -U postgres -d postgres

Si se encuentra un argumento que no pertenece a ninguna opción, se interpretará como el nombre de la base de datos (o el nombre de usuario, si el nombre de la base de datos ya está dado). No todas estas opciones son obligatorias; hay valores predeterminados útiles. Por ejemplo:

Clic ver demo 🖱️


NOTA: Tengamos en cuenta que no se puede simplemente conectarse a cualquier base de datos con cualquier nombre de usuario. El usuario que desea conectarse debe tener los permisos previamente creados por un usuario con rol de administrador o superusuario del sistema de base de datos.

Estableciendo variables de entornos con valores de conexión

Cuando los valores predeterminado no son del todo correctos, puede ahorrarse algo de escritura configurando las siguientes variables de entorno:

PGUSER : (windows cmd)

setx PGUSER postgres

PGPASSWORD : (windows cmd)

setx PGPASSWORD postgre

set variables

NOTA: Considere usar mejor un archivo pgpass

Conexión en formato de URI

Una forma alternativa de especificar los parámetros de conexión es una cadena o un URI tipo conninfo, que se usa en lugar del nombre de una base de datos. Este mecanismo le da un control muy amplio sobre la conexión.

Un ejemplo sería:

psql postgresql://hostname:5432/mydb?user=username
Clic ver demo 🖱️


Archivo de contraseñas

El archivo .pgpass se debe almacenar en el directorio de inicio de un usuario puede contener contraseñas que se utilizarán si la conexión requiere una contraseña (y si no se ha especificado ninguna contraseña). En Windows, el archivo se nombra en el directorio de datos %APPDATA%\postgresql\pgpass.conf (donde %APPDATA% se refiere al subdirectorio de Datos de la aplicación en el perfil del usuario). Como alternativa, el archivo de contraseñas que se utilizará se puede especificar mediante el parámetro de conexión passfile o la variable de entorno PGPASSFILE.

Este archivo debe contener líneas en el siguiente formato:

hostname:port:database:username:password
Clic ver ejemplo estableciendo la variable PGPASSFILE en cmd Windows 🖱️


Podemos agregar un comentario en una línea precediéndola con un #. Cada uno de los primeros cuatro campos puede ser un valor literal, o un comodín * para que coincida con cualquier cosa y de esa manera no nos solicite ingresar credenciales para conectarnos a otra base de datos o desde un cliente como pgadmin que nos muestra un mensaje de error al proporcionar valores literales.

Un ejemplo sería:

# hostname:port:database:username:password
localhost:5432:*:postgres:postgre
Clic ver ejemplo agregando un archivo pgpass desde PgAdmin 🖱️


En los sistemas Unix, los permisos en un archivo de contraseña deben prohibir cualquier acceso al mundo o grupo; lograr esto mediante un comando como chmod 0600 ~/.pgpass. Si los permisos son menos estrictos que esto, el archivo se ignorará. En Microsoft Windows, se supone que el archivo se almacena en un directorio que es seguro, por lo que no se realizan comprobaciones de permisos especiales

Meta Comandos

Cualquier cosa que ingrese en psql que comience con una barra invertida \ sin comillas es un meta-comando de psql que es procesado por psql mismo. Estos comandos hacen que psql sea más útil para la administración o la creación de scripts.

Comando Descripción
\a Activa o Desactiva el formato alineado de la tabla.
\c [params] Establece una nueva conexión a un servidor PostgreSQL.
\C [title] Establece o Anula el título de las tablas que se imprimen en los resultados de una consulta.
\cd [directory] Cambia el directorio de trabajo actual a directory. Sin argumentos, cambia el directorio de inicio del usuario actual.
\conninfo Muestra información sobre la conexión de base de datos actual.
\copyright Muestra información sobre los términos de licencia.
\echo [text] Imprime los argumentos evaluados en la salida estándar, separados por espacios y seguidos de una nueva línea, si se coloca entre (`) se interpreta como un comando.
\timing Activa o Desactiva la visualización del tiempo en milisegundos que tarda cada instrucción SQL.
\x Activa o Desactiva el formato de tabla expandido en el resultado de cada instrucción SQL o meta-comando.
\h - \help [command] Brinda ayuda de sintaxis en el comando SQL especificado. Si command no se especifica psql enumerará todos los comandos para los que hay ayuda disponible. Si command es un asterisco (*), se muestra la ayuda de sintaxis en todos los comandos SQL.
\H - \html Activa el formato de salida de consulta HTML. Si el formato HTML ya está activado, se vuelve al formato de texto alineado predeterminado. Este comando es por compatibilidad y comodidad, pero tenemos \pset para configurar otras opciones de salida.
\! [command] Permite ejecutar comandos de la shell de Linux o de CMD de windows desde la propia consola de psql.
\i - \include Lee la entrada del archivo filename y la ejecuta.
\l - \l+ [pattern] Muestra un listado de las bases de datos que existen en la instancia del servidor PostgreSQL junto a sus nombres, propietarios, juego de caracteres y privilegios de acceso. Si se especifica pattern, solo se listan las bases de datos cuyo nombre coincidan con el patrón. Si se agrega el símbolo "+", también se muestran los tamaños de las bases de datos, los tablespace y las descripciones.
\s [filename] Imprime por consola o en un archivo filename si se especifica, un historial de los comandos utilizados.
\o - \out [filename] Permite guardar el resultado de las futuras consultas en el archivo filename. El resultado incluye todas las tablas, respuestas de comandos y mensajes de tipo "notices" del servidor, pero no los mensajes de error.

Meta Comando para alineación

Si el formato de salida de la tabla no está alineado, se cambia a alineado. Este comando es mantenido por compatibilidad y comodidad, se puede utilizar \pset para una solución más general. Ej:

aligned

Meta Comando para cambiar de conexión

Establece una nueva conexión a un servidor de PostgreSQL. Los parámetros de conexión se pueden especificar usando la sintaxis posicional o usando una cadena de conexión.

connect png

Meta Comando para para establecer un título a las tablas

Establece o Anula el título de las tablas que se imprimen en los resultados de una consulta.

png mc-title

Meta Comando para para imprimir la Licencia

Muestra los términos de copyright y distribución de PostgreSQL

png mc-copyright

Meta Comando para medir el tiempo de las sentencias SQL

Activa o Desactiva la visualización del tiempo en milisegundos que tarda cada instrucción SQL.

timing png

Meta Comando para cambiar la orientación de la salida

Activa o Desactiva el formato de tabla expandido en el resultado de cada instrucción SQL o meta-comando.

gif xtend

Comando Echo para imprimir

echo gif

Meta comando para cambiar la salia a HTML

html gif

\conninfo

timing png

Interpolación SQL

Una característica clave de las variables en psql es que pueden sustituirlas (“interpolarlas”) en sentencias SQL normales, así como en los argumentos de los meta-comandos. Además psql proporciona funciones para garantizar que los valores de las variables utilizados como identificadores y literales de SQL se cite correctamente. La sintaxis para interpolar un valor sin comillas es anteponer dos puntos (:) al nombre de la variable. Por ejemplo:

\set var 'usuarios'
SELECT * FROM :var;

consultaría la tabla con el valor usuarios asignada a la variable. Considerar que esto puede no ser seguro: el valor de la variable se copia literalmente, por lo que puede contener comillas no balanceadas o incluso comandos de barra invertida. Debemos asegurarnos de que el valor tenga sentido donde lo utilicemos.

Cuando se va a utilizar un valor como identificador o literal de SQL, lo más seguro es disponer que se incluya entre comillas. Para citar el valor de una variable como literal SQL, escriba dos puntos (:) seguidos del nombre de la variable entre comillas simples. Para citar el valor como un identificador SQL, escriba dos puntos seguidos del nombre de la variable entre comillas dobles. Estas construcciones tratan correctamente las comillas y otros caracteres especiales incrustados en el valor de la variable. El ejemplo anterior se escribiría de manera más segura de esta manera:

\set var 'usuarios'
SELECT * FROM :"var";

La interporlación de variables no se realizará dentro de literales e identificadores de SQL entre comillas. Por lo tanto, una construcción como ':var' no funciona para producir un literal entrecomillado a partir del valor de una variable (y no sería seguro si funcionara, ya que no manejaría correctamente las comillas incrustadas en el valor).

Un ejemplo de uso de este mecanismo es copiar el contenido de un archivo en una columna de tabla. Primero se carga el archivo en una variable y luego interpolamos el valor de la variable como una cadena entrecomillada:

\set content `cat my_file.txt`
INSERT INTO my_table VALUES (:'content');


\set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '