PostgreSQL.txt

=====================
Guía PostgreSQL by dM
=====================

===========
Instalacion
===========

Actualizar los repositorios

# aptitude update

-----

Buscar la version actual en los repositotios

# aptitude search postgresql

-----

Instalar la version de PostgreSQL

# aptitude install postgresql postgresql-server

-----

Reiniciar el servicio de PostgreSQL, para aplicar cambios etc.

$ sudo service postgresql restart

ó

$ sudo /etc/init.d/postgresql restart

=========
Monitoreo
=========

Ver el estatus del servidor de postgresql

$ service postgresql status

$ sudo /etc/init.d/postgresql restart

==================
Remover postgresql
==================

Los siguientes paquetes se instalan por defecto en Debin Jessie 8, por eso
los removemos manualmente, pero aplicará a las versiones que tenga instaladas,
lo sabremos con un $ aptitude search postgresql

# apt-get --purge remove postgresql-9.4 postgresql-client-9.4 postgresql-client-common postgresql-common

==================================
Entrar en la consola de postgresql
==================================

$ su

# su postgres

# psql // Entrar a posgresql

ó (sí está instalado sudo)

$ sudo su postgres
postgresql@xxx:$ psql

---

Al ver esto postgresql fue instalado correctamente

psql (9.x.x)
Type "help" for help.

-----

Conectarse diréctamente a una db desde la línea de comandos.

postgres@xxx:$ psql name_db

-----

Línea de comandos de postgresql

postgres=#

-----

postgres=# help // Ayuda de postgresql
Está usando psql, la interfaz de línea de órdenes de PostgreSQL.
Digite:  \copyright para ver los términos de distribución
       \h para ayuda de órdenes SQL
       \? para ayuda de órdenes psql
       \g o punto y coma («;») para ejecutar la consulta
       \q para salir       
postgres=# 

-----

Salir de la consola de Postgres

# \q

-----

Salir de la consola de Postgres

# \quit

-----

Para ver los roles existentes (usuarios)

# \du

-----

Ver las bases de datos creadas

# \l

-----

# \s // Ver el historial de comandos usados en postgres

Ctrl + l // Limpiar la consola de postgres

-----

Muestra la fecha del sistema, para ver el formato de fecha de postgres.

# SELECT CURRENT_DATE;

-----

Crear usuario sin password, es mejor asignarle el pass

# CREATE USER nombre_usuario;

-----

Crear usuario y asignarle el password

# CREATE USER nombre_usuario PASSWORD 'p@s5w0rd';

-----

Asignarle o cambiarle el password a un usuario, debe estar creado

# ALTER USER usuario WITH PASSWORD 'nueva_password';

-----

Borrar un usuario

# DROP USER nombre_usuario;

-----

Crear una base de datos

# CREATE DATABASE nombre_db;

-----

Crear una copia de una database existente

# CREATE DATABASE new_db WITH TEMPLATE original_db OWNER db_user;

-----

Borrar una base de datos

# DROP DATABASE nombre_db;

-----

Creando una base de datos, debe estar creado el usuario

# CREATE DATABASE nombre_BaseDatosprueba OWNER nombre_usuario;

----

Conectarse a la base de datos creada

# \c name_db;

-----

Conectarse de nuevo a la db postgres con el usuario postgres 

# \c postgres;

-----

Creacion de una tabla con un campo varchar de tamaño 20 y un campo para enteros.

name_db=# CREATE TABLE nombre_tabla (nombre varchar(20),edad int);

-----

Para ver la lista de relaciones de la db, se debe estar conectado a la db, se
pueden ver las tablas

name_db=# \d

-----

Para ver la descripción (nombre de columnas, tipo de datos de una tabla, etc.

namedb=# \d nombre_tabla

-----

Insertar datos en la tabla

namedb=# INSERT INTO nombre_tabla (nombre_campo, ..., nombre_campo) values (valor_campo, ..., valor_campo);

Ejemplo de Insertar datos en una tabla, las cadenas se expresan entre ('').:

namedb=# INSERT INTO usuario (nombre, nacionalidad, cedula) values ('vladimirov','V',12345678);

-----

Insertar datos, no se pueden dejar campos vacios.

namedb=# INSERT INTO usuarios VALUES ('mariana','V',12345678);

-----

Hacer una consulta a los datos de un campo especifico.

namedb=# SELECT nombre_campo FROM nombre_tabla;

-----

Hacer una consulta a los datos de varios campos especificos, separados por ",".

namedb=# SELECT nombre_campo,nombre_campo2 FROM nombre_tabla;

-----

Hacer una consulta de los datos de la tabla, (*) Significa que lea todas las
columnas de la tabla.

namedb=# SELECT * FROM nombre_tabla;

-----

Hacer una consulta a los registros de una tabla especificando el número de
registros a consultar donde n es un entero, ejemplo 5.

namedb=# select * from nombre_tabla limit n;

Consultar todos los registros de una tabla pero ordenándolos ascendentemente en
referencia a un campo (id) en este caso.

namedb=# SELECT * FROM name_table ORDER BY id;

-----

Consultar los registros de una tabla ordenandolos de manera ascendente en
referencia a un campo específico.

namedb=# SELECT * FROM name_table ORDER BY id ASC;

-----

Consultar los registros de una tabla ordenandolos de manera descendente en
referencia a un campo específico.

namedb=# SELECT * FROM name_table ORDER BY id DESC;

-----

Hacer una consulta de los datos de una tabla cuando coincida con el parametro
establecido, cadena

namedb=# SELECT * FROM nombre_tabla WHERE nombre_campo='valor';

-----

Hacer una consulta de los datos de una tabla cuando el registro contenga la
cadena o valor a buscar.

# SELECT * FROM auth_permission WHERE codename LIKE '%email%';

Resultado: Registros que contenienten la cadena 'email'.

 id |       name       | content_type_id |   codename
----+------------------+-----------------+--------------
  1 | Can add email    |               1 | add_email
  2 | Can change email |               1 | change_email
  3 | Can delete email |               1 | delete_email
  4 | Can view email   |               1 | view_email

-----

Hacer una consulta de los datos de una tabla cuando coincida con el parametro
establecido, entero

namedb=# SELECT * FROM nombre_tabla WHERE nombre_campo=3;

-----

Consultar los datos de una tabla que sean mayores o iguales que.

name_db=# SELECT * FROM name_table WHERE id >= 5;

-----

Ordenar ascendente los registros mayores que.

db_name=# select id from table where id>=25 order by id ASC;

-----

Listar los registros de una tabla que esten en el rango de 25 y 27.

db_name=# select id from table WHERE id IN ( 25, 27 );

-----

Listar los registros de una tabla exepto los que sean iguales a 25 y 27.

db_name=# select id from table WHERE id NOT IN ( 25, 27 );

-----

Listar los registro de una tabla cuyo valor del id esté entre 2 y 5 incluyéndo
los mismos.

db_name=# select * from table WHERE id BETWEEN 2 and 5;

-----

Listar todos los registros cuyo name inicie con la porción de cadena 'Pa'.

db_name=# SELECT * FROM table WHERE name LIKE 'Pa%';

-----

Listar todos los registros cuyo name inicie con la porción de cadena 'Pa'.

db_name=# SELECT name FROM table WHERE name LIKE 'Pa%';

-----

Hacemos una consulta a una tabla, pero usamos la palabra AS para crer un alias
para los nombres de los campos, muy util para generar consultas personalizadas y
crear un .csv para hacer una migración de datos, por ejemplo.

db_name=# select id as identificador,title as título from table_noticia;

------

Al crear un usuario podemos darle permisos a una base de datos existente.

postgres=# GRANT ALL PRIVILEGES ON DATABASE nombre_BaseDatos TO nombre_usuario;

-----

Cambiar los permisos de un usuario;

postgres=# ALTER ROLE name_user WITH SUPERUSER;

where option can be:

    | SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

-----

Eliminar los registros de una tabla

nombredb=# DELETE FROM nombre_tabla;

-----

Borrar los datos de la columna de una tabla cuando se cumpla una condicion.

namedb=# DELETE FROM table_name WHERE condition;

-----

Eliminar los registros de un campo especifico

nombredb=# DELETE FROM nombre_tabla WHERE nombre_campo='xxx';

Ejemplo:

En este ejemplo eliminaremos los registros de una tabla usuarios pero solo de
los campos cuyo nombre sea igual a (vladimirov), por ello se usa la clausula
WHERE.

nombredb=# DELETE FROM usuario WHERE nombre='vladimirov';

-----

Vaciar los registros de una tabla, (elimina todos los registros de la tabla) y
vuelve a crear la tabla con la misma estructura.

nombredb=# TRUNCATE TABLE nombre_table;

La diferencia con "delete" es la velocidad, es más rápido "truncate table" que
"delete" (se nota cuando la cantidad de registros es muy grande) ya que éste
borra los registros uno a uno.

-----

Elimina una tabla asi como sus registros.

nombredb=# DROP TABLE nombre_tabla

-----

Agregar un nuevo campo a una tabla.

nombredb=# ALTER TABLE nombre_tabla ADD nombre_campo tipo_campo;

-----
En la base de datos prueba, agregamos a la tabla usuarios un nuevo campo cedula de tipo entero.
prueba=# ALTER TABLE usuarios ADD cedula int;

-----

Cambiar o renombrar el nombre de un campo creado.

nombredb=# ALTER TABLE nombre_tabla RENAME nombre_campo TO nuevo_nombre_campo;

-----

Para eliminar un campo o una columna de una tabla.

nombredb=# ALTER TABLE nombre_tabla DROP COLUMN nombre_columna;

-----

Para eliminar un campo o una columna de una tabla en cascada.

nombredb=# ALTER TABLE nombre_tabla DROP COLUMN nombre_columna CASCADE;

-----

Modificar el tipo de dato de una columna.

nombredb=# ALTER TABLE nombre_tabla ALTER COLUMN nombre_campo TYPE tipo_campo;

-----

Modificar el registro de un campo

nombredb=# UPDATE nombre_tabla SET nombre_campo='xxx' WHERE nombre_campo='xxxx';

Ejemplo:

En la base de datos prueba, vamos a actualizar un registro de la tabla usuarios
que corresponde a la columna o campo nombre, el registro que se modificara sera
el que corresponda con el parametro pasado con WHERE, sera el parametro de
busqueda en la columna, pero puede ser el id por ejemplo.

Prueba=# UPDATE usuarios SET nombre='Vitaly' WHERE cedula='12345678';

------

Cambiar el nombre de la database

# ALTER DATABASE name RENAME TO new_name

------

Cambiar el dueño de la database

# ALTER DATABASE name OWNER TO new_owner

-----

Creando columna de autoincremento #####

Para crear una columna de autoincremento, ideal para la columna (id) se usa el
tipo de dato (serial)

Ejemplo:
 
Creamos una tabla usuarios con dos campos, id que sera (serial) y otro campo
nombre de tamaño 20, el campo serial toma el valor por defecto de (1) al momento
de comenzar a introducir registros en los campos.

nombredb=# CREATE TABLE usuarios (id serial, nombre varchar(20));

Ejemplo:
prueba=# INSERT INTO usuarios (nombre) VALUES ('Kristina');
prueba=# INSERT INTO usuarios (nombre) VALUES ('Khalifa');

Introducimos los registros 'Kristina' y 'Khalifa' en la columna nombre, como
vemos no hace falta introducir el valor de la columna (id) puesto que lo toma
automaticamente autoincrementando en relacion al anterior registro. Si
observamos la tabla se vería asi:

prueba=# SELECT * FROM usuarios;
 id | nombre     
 ----+-------+
1 | Kristina |
2 | Khalifa  |
(2 filas)

-------

Creando un campo id tipo clave primaria.

nombredb=# CREATE TABLE nombre_tabla (id serial primary key, nombre_campo varchar(20));

=============================================
Hacer un dump o respaldo de una base de datos
=============================================

Significa hacer una copia de seguridad de una base de datos, generando un
fichero .sql que contiene todos los comandos y sentencias para crear los
esquemas de una base de datos, además de albergar registros previos, si
existiesen.

$ pg_dump -U username -W -h host databasename > databasename.sql

Recomendado éste.

$ pg_dump -U username -W -h 127.0.0.1 databasename > basename.sql

$ pg_dump -U username db_name > db_ename.sql

$ pg_dump -U usuario -h 127.0.0.1 nombre_database > nombre_database.sql

postgresql dump ERROR: permiso denegado a la relación name_table
================================================================

A veces no deja hacer el dump, porque dice que el usuario no tiene permisos en
la tabla específica, entonces escribimos el siguiente comando:

name_db=# GRANT SELECT ON name_table TO PUBLIC;

======================================
Restaurar / Importar una base de datos
======================================

Para restaurar, Se debe tener creado el usuario con la contraseña y la base de
datos, luego el dump sustituirá a la bd creada

$ psql -h localhost -U nombre_usuario -d nombre_bd -f /ruta/al/archivo.sql

Parametros:
-h : Es la ip en donde esta el servidor de la base de datos, si esta trabajando
localmente entonces es localhost o 127.0.0.1
-U : Nombre de usuario propietario de la base de datos.
-d : Nombre de la base de datos.
-f : Ruta del archivo sql, si esta en el directorio no hay que poner la ruta

Ejemplo:

$ psql -h 127.0.0.1 -U usuario_cliente -d banco_db -f banco_db.sql

Otra forma
==========

Se debe tener creado el usuario con la contraseña y la base de datos, luego el
dump sustituirá a la bd creada

$ psql -U username -W -h localhost nombre_base < dump_base.sql

-U Se refiere al Usuario, en este caso puede ser el usuario propietario de la
base de datos o 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 obtener nuestro dump, si estamos local podemos colocar localhost sino
ponemos la IP del servidor PostgreSQL

databasename Este es el ultimo parámetro realmente en esta linea de comando, por
esa razon no tiene alguna letra que indique que el siguiente parámetro es el
nombre de la base de datos basename.sql Esta parte en realidad solo indica que
la salida de nuestro comando pg_dump la guarde en un archivo databasename.sql

========================================
Restaurando .sql desde psql command line
========================================

postgres=# \i [full path and file name with extension]

Desde la db postgres (por defecto) importamos el .sql que creará la db
automáticamente, con el nombre y el dueño originales

postgres=# \i /home/user/dump.sql 

Conectado a la nueva db vacía, con el usuario y contraseña correctos, es decir,
los originales, hacemos la restauración.

name_db=# \i /home/user/dump.sql

===============================================
Restaurando datos de una tabla con archivo .sql
===============================================

$ psql -h localhost -U <username> -d <dbname> -1 -f <filename>.sql

===========================================
Copiar datos de una tabla a un fichero .csv
===========================================

Creamos un fichero .csv, en este ejemplo será "x.csv" y lo guardamos en el
/home/user/ luego le damos permisos con:

$ touch x.csv

$ sduo chmod 777 x.csv

Luego, desde la línea de comandos de postgres, nos conectamos a la db y ahí
ponemos el comando de copia de la tabla que queremos:

Nos pasará los datos de la name_tabla al fichero .csv, separando los datos por
filas y comas ",". Si se lanza de nuevo el comando el fichero se sobreescribe,
no se duplica la data.

db_name=# \copy name_table to 'x.csv' csv header;

-----

Pasándole la ruta absoluta del fichero.

db_name=# \copy name_table to '/home/user/x.csv' csv header;

-Nota: A veces falla en la copia: xxxxx.csv: Permiso denegado, por lo que se recomienda mover el .csv a /home/user/tmp/ para que no haya problema, y en el comando meter la ruta absoluta del fichero.

==========================================================
Copiar datos de una tabla where x='xxxx' a un fichero .csv
==========================================================

Copiando todos los campos.

db_name=# \COPY (select * from name_table WHERE x='xxxx') to 'x.csv' csv header;

-----

Especifícando un campo.

db_name=# \COPY (select id from name_table WHERE x='xxxx') to 'x.csv' csv header;

-----

Especifícando mas de un campo.

db_name=# \COPY (select id,username from name_table WHERE x='xxxx') to 'x.csv' csv header;

######################################################
##### Copiando columnas específicas de la tabla  #####
######################################################

Crear el .csv y darle permisos.

db_name=# \COPY (select id from name_table) TO 'x.csv' DELIMITER ',' CSV HEADER;

db_name=# \COPY (select id,username from name_table) TO 'x.csv' DELIMITER ',' CSV HEADER; // Copiando dos columnas específicas en el .csv

Se puede usar el comando COPY sin "\" aunque a veces no permite las copias por los permisos insuficientes, asi que se usa "\".

##########################################################
##### Copiando datos de un .csv a una tabla de la db #####
##########################################################

El fichero csv debe contener la estructura adecuanda (campos), con las columnas y las comas así como la data, por ejemplo:

---

id,username
1,admin

---

db_name=# \COPY name_table FROM '/home/user/x.csv' CSV header;

#########################
##### Configuración #####
#########################

Los archivos de configuracion estan en: 

--> /etc/postgresql/9.1/main

######################
##### phppgadmin #####
######################

PhpPgAdmin es una aplicación web, escrita en PHP, para administrar bases de datos PostgreSQL.
Es una aplicación web que provee una interfaz grafica a los usuarios para crear bases de datos, tablas, alterarlas y consultar
sus datos usando el lenguaje estándar SQL. phpPgAdmin estuvo basado en phpMyAdmin, pero hoy día ya no comparte código con él; incluso
provee las mismas funcionalidades y más a los usuarios del servidor de base de datos PostgreSQL.

Instalacion:

# aptitude install phppgadmin

http://localhost/phppgadmin/ // Interfaz grafica en el navegador web

El usuario y contraseña para conectarse debe ser un usaurio de postgres.

Por ahora me ha servido para reiniciar las secuencias de las tablas de django.

Si hay 3976 registros se debe configurar que el último valor es 3977 para que guarde bien en la tabla.

===========
pgadmin III
===========

pgAdmin is the leading graphical Open Source management, development and
administration tool for PostgreSQL.

# aptitude install pgadmin3

--

Cambiar la contraseña del usuario de postgres

postgres=# ALTER USER postgres WITCH PASSWORD '123456';

--

Pgadmin nos permite generar un json a partir de los datos de una tabla, con una
secuencia sql directamente:

SELECT array_to_json(array_agg(name_table)) FROM name_table;

##############################
##### Boolean Data Type ######
##############################

Valid literal values for the "true" state are:
TRUE
't'
'true'
'y'
'yes'
'on'
'1'
For the "false" state, the following values can be used:
FALSE
'f'
'false'
'n'
'no'
'off'
'0'

##########################################################################
##### Conversión de timestamp Unix a timestamp o date de PostgreSQL ######
##########################################################################

namedb=# select to_timestamp(1480973718);

########################
##### fichero .sql #####
########################

Los comentarios dentro un fichero .sql se escriben con: 

-- Comentario línea 1
-- Comentario línea 2

############################################
##### Creando relaciones en las tablas #####
############################################

En el siguiente ejemplo tenemos dos tablas, una de entidades(estados) y otra de municipios, cada tabla tiene una llave
primaria, pero la tabla de municipios esta relacionada con la llave primaria de la tabla de entidades.

CREATE TABLE entidad (
  codigo_entidad varchar(100) PRIMARY KEY,
  nombre varchar(100));

CREATE TABLE municipio (
    codigo_municipio varchar(100) PRIMARY KEY,
    nombre varchar(100),
    codigo_entidad integer REFERENCES entidad
);

Añadiendo la tabla parroquia a la secuencia de relaciones.

CREATE TABLE parroquia (
    codigo_parroquia varchar(100) PRIMARY KEY,
    nombre varchar(100),
    codigo_municipio varchar REFERENCES municipio
);

====================================================
Cambiar codificación de una base de datos (encoding)
====================================================

postgres# update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name';

// Para LATIN9
postgres=# update pg_database set encoding=16 where datname='db_name';

// Para LATIN1
postgres=# update pg_database set encoding=16 where datname='db_name';

Instalando postgresql en chroot

Si sale el error:

psql: error: no se pudo conectar al servidor: no se pudo conectar con el
servidor: No existe el fichero o el directorio

Se debe reiniciar el servicio de postgresql

# service postgresql restart

=======
PostGIS
=======

PostGIS es un módulo para Postgresql.

PostGIS convierte al sistema de administración de bases de datos PostgreSQL en
una base de datos espacial mediante la adición de tres características: tipos de
datos espaciales, índices espaciales y funciones que operan sobre ellos. Debido
a que está construido sobre PostgreSQL, PostGIS hereda automáticamente las
características de las bases de datos empresariales, así como los estándares
abiertos que implementan un Sistema de Información Geográfica dentro del motor
de base de datos.

Ha sido desarrollado por la empresa canadiense Refraction Research,
especializada en productos "Open Source" entre los que habría que citar a Udig.
PostGIS es hoy en día un producto veterano que ha demostrado versión a versión
su eficiencia. En relación con otros productos, PostGIS ha demostrado ser muy
superior a la extensión geográfica de la nueva versión de MySQL, y a juicio de
muchos, es muy similar a la versión geográfica de la base de datos Oracle.

Un aspecto que debemos tener en cuenta es que PostGIS ha sido certificado en
2006 por el Open Geospatial Consortium (OGC) lo que garantiza la
interoperabilidad con otros sistemas también interoperables. PostGIS almacena la
información geográfica en una columna del tipo GEOMETRY, que es diferente del
homónimo "GEOMETRY" utilizado por PostgreSQL, donde se pueden almacenar la
geometría en formato WKB (Well-Known Binary), aunque hasta la versión 1.0 se
utilizaba la forma WKT (Well-Known Text).

============================================================================
Agregar el repositorio oficial de Postgresql para instalar últimas versiones
============================================================================

Create the file repository configuration:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package lists:

$ sudo apt-get update

Install the latest version of PostgreSQL.

$ sudo apt-get -y install postgresql

=================
La expresión LIKE
=================

LIKE devuelve verdadero si la cadena coincide con el patrón proporcionado.
(Como se esperaba, la expresión NOT LIKE devuelve falso si LIKE devuelve
verdadero, y viceversa.

Si el patrón no contiene signos de porcentaje o guiones bajos, entonces el
patrón solo representa la cadena en sí; en ese caso LIKE actúa como el operador
igual.

Algunos ejemplos:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

==========================
Los esquemas en postgresql
==========================

Los esquemas son como "agrupaciones" de tablas. Por ejemplo podrías tener un
sistema multicompañía, y en una misma base de datos pero con diferentes esquemas
mantener la infomación. Por ejemplo compania1.clientes y compañia2.clientes

Las tablas clientes tendrían la misma estructura y el mismo nombre pero
almacenadas en esquemas diferentes.

En PostgreSQL, los esquemas son una forma de organizar y agrupar objetos dentro
de una base de datos. Actúan como contenedores que permiten gestionar tablas,
vistas, índices, funciones y otros objetos de manera ordenada, separándolos por
contexto o propósito dentro de una misma base de datos.

Características principales de los esquemas:

Separación lógica: Ayudan a organizar los objetos en una base de datos de forma
lógica, evitando conflictos de nombres entre tablas o vistas que cumplen
propósitos diferentes.

Control de acceso: Puedes asignar permisos específicos a cada esquema,
controlando quién puede acceder y modificar los objetos dentro de ellos.

Espacios de nombres: Cada esquema funciona como un espacio de nombres
independiente. Por ejemplo, podrías tener dos tablas con el mismo nombre en
diferentes esquemas (esquema1.tabla y esquema2.tabla).

Predeterminado: PostgreSQL crea automáticamente un esquema predeterminado
llamado public. Si no se especifica un esquema al crear un objeto, se utiliza
este por defecto.

Uso en consultas: Puedes referenciar un objeto en un esquema usando la notación
esquema.objeto. Por ejemplo:

# SELECT * FROM esquema1.mi_tabla;

Ventajas de usar esquemas:

Organización: Facilita estructurar bases de datos grandes con múltiples objetos.

Modularidad: Permite agrupar objetos relacionados y manejarlos como una unidad.

Colaboración: Diferentes equipos pueden trabajar en diferentes esquemas sin
interferir.

Seguridad: Puedes restringir el acceso a un esquema completo en lugar de
administrar permisos objeto por objeto.

Crear esquemas
==============

# CREATE DATABASE test_schema OWNER admin;

# \c test_schema

Para crear un esquema, se utiliza el comando CREATE SCHEMA:

# CREATE SCHEMA nombre_del_esquema;

Ejemplo de uso:

-- Crear dos esquemas
CREATE SCHEMA ventas;
CREATE SCHEMA inventario;

-- Crear una tabla en el esquema "ventas"
CREATE TABLE ventas.clientes (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    email VARCHAR(100)
);

-- Crear una tabla en el esquema "inventario"
CREATE TABLE inventario.productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    cantidad INT
);

-- Consultar datos de una tabla en un esquema específico
SELECT * FROM ventas.clientes;

Eliminar un esquema:

DROP SCHEMA nombre_del_esquema CASCADE;
(Usar CASCADE elimina también todos los objetos dentro del esquema).

Ver los esquemas existentes:

# \dn

Los esquemas son esenciales para bases de datos complejas y multiusuario, ya
que permiten mantener un alto nivel de organización y control.

Ejemplo con 2 compañias
=======================

# CREATE DATABASE test_schema OWNER admin;

# \c test_schema

CREATE SCHEMA company_1;

CREATE SCHEMA company_2;

-- Crear una tabla en el esquema "company_1"
CREATE TABLE company_1.clientes (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    email VARCHAR(100)
);

-- Crear una tabla en el esquema "company_2"
CREATE TABLE company_2.productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    cantidad INT
);

# SELECT * FROM compani_1.clientes;

 id | nombre | email
----+--------+-------
(0 filas)

# SELECT * FROM compani_2.clientes;

id | nombre | email
----+--------+-------
(0 filas)

postgres=# \dn

 Listado de esquemas
  Nombre   |  Dueño
-----------+----------
 compani_1 | postgres
 compani_2 | postgres
 public    | postgres

# INSERT INTO compani_1.clientes (id, nombre, email) VALUES (1, 'Juan Pérez', 'juan.perez@example.com');
INSERT 0 1

# INSERT INTO compani_2.clientes (id, nombre, email) VALUES (1, 'Maria Angulo', 'mangulo@example.com');
INSERT 0 1

# SELECT * FROM compani_1.clientes;
 id |   nombre   |         email
----+------------+------------------------
  1 | Juan Pérez | juan.perez@example.com
(1 fila)

# SELECT * FROM compani_2.clientes;
 id |    nombre    |        email
----+--------------+---------------------
  1 | Maria Angulo | mangulo@example.com
(1 fila)

Notas importantes:

Esquema predeterminado: Si omites el nombre del esquema en el INSERT, PostgreSQL
intentará usar el esquema predeterminado (public en la mayoría de los casos).
Por lo tanto, debes especificar el esquema para evitar confusiones.

Verificación de esquemas:

Si tienes dudas sobre las tablas disponibles en cada esquema, puedes usar:

# \dt compani_1.*
          Listado de relaciones
  Esquema  |  Nombre  | Tipo  |  Dueño
-----------+----------+-------+----------
 compani_1 | clientes | tabla | postgres
(1 fila)

# \dt compani_2.*
          Listado de relaciones
  Esquema  |  Nombre  | Tipo  |  Dueño
-----------+----------+-------+----------
 compani_2 | clientes | tabla | postgres
(1 fila)

Esto mostrará todas las tablas en el esquema correspondiente. ¡Con esto podrás
asegurarte de que estás insertando en la tabla correcta!

Fuente
======

ChatGPT

====================================
¿Cómo otorgar acceso a los usuarios?
====================================

Aquí hay algunas declaraciones comunes para otorgar acceso a un usuario de
PostgreSQL:

1) Otorgue CONNECT a la base de datos:

GRANT CONNECT ON DATABASE database_name TO username;

2) Otorgar USAGE en el esquema:

GRANT USAGE ON SCHEMA schema_name TO username;

3) Otorgar en todas las tablas para declaraciones DML: SELECT, INSERT, UPDATE,
DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

4) Otorgue todos los privilegios en todas las tablas del esquema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

5) Otorgue todos los privilegios en todas las secuencias del esquema:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

6) Otorgue todos los privilegios en la base de datos:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

7) Otorgue permiso para crear una base de datos :

ALTER USER username CREATEDB;

8.Convierta un usuario en superusuario :

ALTER USER myuser WITH SUPERUSER;

9) Eliminar el estado de superusuario :

ALTER USER username WITH NOSUPERUSER;

Esas declaraciones anteriores solo afectan las tablas existentes actuales

Para aplicar a tablas recién creadas, debe usar alter default Por ejemplo:

ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

Fuente
======

https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html

======================
Booleano en PostgreSQL
======================

Hay tres tipos de valores booleanos: VERDADERO, FALSO y NULO. Donde NULL
significa desconocido.

Booleano En SQL, puede usar VERDADERO y FALSO sin comillas, y también puede usar
caracteres entre comillas que representan verdadero y falso, como: 'sí', 'no',
'1', '0'.

Ejemplo
=======

1. Construir una mesa

CREATE TABLE test (
  id  int,
  coll boolean,
  collName varchar(20)
);

2. Insertar datos

insert into test values(1,TRUE,'TRUE');
insert into test values(2,FALSE,'FALSE');

insert into test values(3,true,'true');
insert into test values(4,false,'false');

insert into test values(5,'yes','''yes''');
insert into test values(6,'no','''no''');

insert into test values(7,'1','''1''');
insert into test values(8,'0','''0''');

3. Consultar datos

# select * from test;

|| id || coll || collNmae ||
|| 1  || t    || TRUE ||
|| 2  || f    || FALSE ||
|| 3  || t    || true ||
|| 4  || f    || false ||
|| 5  || t    || 'yes' ||
|| 6  || f    || 'no' ||
|| 7  || t    || '1' ||
|| 8  || f    || '0' ||

Fuente
======

https://programmerclick.com/article/3772240389/

======================================================================
Agregar repositorio de Postgresql para actualizar paquetes de Postgres
======================================================================

$ sudo apt -y install vim bash-completion wget

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

$ sudo apt update

Instalar lo que se necesite.

===================================
Consultar los índices en PostgreSQL
===================================

PostgreSQL puedes consultar los índices existentes en tu base de datos
utilizando las vistas del catálogo del sistema, como pg_indexes. Estas vistas
contienen información sobre los índices creados en las tablas.

Comando para ver los índices de toda la base de datos, primero debe conectarse a
la base de datos que requiera:

SELECT * FROM pg_indexes;

Esto mostrará una lista de todos los índices en la base de datos actual,
incluyendo las columnas:

schemaname: Esquema al que pertenece la tabla.
tablename: Nombre de la tabla.
indexname: Nombre del índice.
indexdef: Definición del índice.

Ver índices de una tabla específica
===================================

Si deseas listar solo los índices de una tabla en particular, puedes usar un
filtro:

SELECT * FROM pg_indexes WHERE tablename = 'nombre_de_tu_tabla';

Detalle sobre índices usando \d en psql
=======================================

Si estás trabajando en la consola de PostgreSQL (psql), puedes usar el comando
\d para ver los índices de una tabla específica:

\d nombre_de_tu_tabla

Esto mostrará detalles sobre la estructura de la tabla, incluyendo los índices
asociados.

Consultar índices con detalles avanzados
========================================

Si necesitas información más detallada sobre los índices, como su tipo o si son
únicos, puedes usar una consulta que combine otras vistas del sistema, como
pg_class y pg_index:

SELECT
    i.relname AS index_name,
    a.attname AS column_name,
    ix.indisunique AS is_unique,
    ix.indisprimary AS is_primary
FROM
    pg_class t
    JOIN pg_index ix ON t.oid = ix.indrelid
    JOIN pg_class i ON i.oid = ix.indexrelid
    JOIN pg_attribute a ON a.attnum = ANY(ix.indkey)
WHERE
    t.relname = 'nombre_de_tu_tabla';
Esto te mostrará:

index_name: El nombre del índice.

column_name: Las columnas asociadas al índice.

is_unique: Si el índice es único (TRUE o FALSE).

is_primary: Si el índice corresponde a la clave primaria.

Ejemplo práctico
================

Si tienes una tabla llamada clientes, puedes consultar los índices asociados
con:

SELECT * FROM pg_indexes WHERE tablename = 'clientes';

O, en la consola de PostgreSQL:

\d clientes

Ambos métodos te permitirán identificar los índices existentes y evaluar si
necesitas crear nuevos índices para optimizar el rendimiento de tus consultas.

Fuente
======

ChatGPT