Gnu/Linux‎ > ‎Servidores‎ > ‎

PSQL, Primeros pasos y Comandos básicos

publicado a la‎(s)‎ 15 jun. 2016 18:08 por Juan Jose Ramirez Lama   [ actualizado el 8 ago. 2017 8:54 ]
PSQL es el entorno de terminal para el manejo del motor de Base de Datos PostgreSQL. Los datos aquí presentados son basados en los que puedes encontrar en http://viviryaprenderweb.com

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)

Ahora bien a veces necesitamos saber alguna información extra de nuestra base como las tablas que la componen, los campos de alguna tabla, etc., para eso existen los siguientes comandos:

El equivalente de SHOW TABLES es
basename=# \d

El equivalente de SHOW DATABASES es
basename=# \l

El equivalente de SHOW COLUMNS es
basename=# \d table

El equivalente de DESCRIBE TABLE es
basename=# \d+ table

Seleccionar una base de datos o cambiar de base:
basename=# \c basename

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.

Mostrar la lista de las tablas de la base de datos que tengamos seleccionada:
basename=# \dt

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 indice sino agregamos RESTART IDENTITY nuestros indices no seran reiniciados y seguiran 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;

Salir del cliente psql:
basename=# \q

Cómo importar una base de datos en PostgreSQL

El comando para importar una base de datos en postgresql es el siguiente:
$ psql -U postgres -W -h localhost nombre_base < dump_base.sql
Como vemos utilizamos el comando psql con los siguientes parámetros:
    • -U Se refiere al Usuario, en nuestro ejemplo usamos el usuario:  postgres
    • -W Con este parámetro conseguiremos que nos solicite el password del usuario antes especificado
    • -h Con este indicamos cuál es el servidor PostgreSQL al que nos conectaremos para importar nuestro dump, si estamos en el mismo servidor podemos colocar localhost, si será un servidor remoto colocaremos la IP
    • nombre_base Este es el ultimo parámetro en nuestra linea del comando, el cual hace referencia al nombre de la base de datos a la que importaremos nuestro dump.
    • < dump_base.sql Indicamos cual es el archivo que contiene el dump de la base y que por tanto queremos importar.



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



Comments