PostgreSQL

Ver todos los ENUMs registrados en la base de datos SELECT * FROM pg_type WHERE typcategory = 'E'; Ver todos los posibles valores para un ENUM determinado Suponiendo que nuestro ENUM se llame “status”… SELECT unnest(enum_range(NULL::status));

Acceder a un campo en una columna JSONB Supongamos que tenemos la tabla items, con la columna properties que es una columna JSONB con una fila que contiene estos datos: { "datetime": "2023-05-02T11:28:29.341176", "name": "Item 32", "type": "Petrol Engine", "price": 1043.25 } Si quisíéramos acceder al campo name del item con id 12345, la consulta sería: SELECT id, properties->>'name' FROM item WHERE id = 12345; Modificar valor de un campo en una columna JSONB Si en la columna mencionada anteriormente quisiéramos ahora cambiar el campo price de la fila de dicho item 12345, la consulta sería: ...

Suponiendo que tengamos un fichero, o simplemente un string, con GeoJSON, como por ejemplo: { "type": "MultiPoint", "coordinates": [ [-1.6402482999999999, 37.411843399999995], [-1.645345, 37.413398199999996], [-1.6453499999999999, 37.4129673], [-1.6453347, 37.41289], [-1.6455814, 37.4127428], [-1.6455834, 37.4136486], [-1.6402482999999999, 37.411843399999995] ] } y una base de datos PostGIS donde tengamos una tabla llamada field con un campo geometry, podríamos hacer una consulta para obtener todos los fields que intersecten con la geometría definida en el GeoJSON: SELECT field.id, field.name FROM field WHERE ST_Intersects(field_zone.geometry, ST_GeomFromGeoJSON(' { "type": "MultiPoint", "coordinates": [ [-1.6402482999999999, 37.411843399999995], [-1.645345, 37.413398199999996], [-1.6453499999999999, 37.4129673], [-1.6453347, 37.41289], [-1.6455814, 37.4127428], [-1.6455834, 37.4136486], [-1.6402482999999999, 37.411843399999995] ] }')); Los métodos de PostGIS utilizados son ST_Intersects, que comprueba intersección entre 2 objetos geometry o geography, y ST_GeomFromGeoJSON, que construye un objecto geometry a partir de su representación en GeoJSON. ...

En ocasiones podemos querer comprobar si el valor de una columna en una tabla existe en otra tabla. Sin que exista una relación de clave entre ellas. Por ejemplo, podríamos tener las tabla: CREATE TABLE usuario ( id serial PRIMARY KEY, nombre VARCHAR (50) NOT NULL, email VARCHAR (255) NOT NULL ); CREATE TABLE post ( id serial PRIMARY KEY, id_usuario INT NOT NULL, titulo VARCHAR (100) NOT NULL, contenido TEXT NOT NULL ); La tabla post tiene una columna id_usuario donde se debería poner el id del usuario que ha escrito el post. Sin embargo no es una Foreign Key, con lo cual no hay manera de saber si todos los post.ids concuerdan realmente con un usuario.id o si son números que no existen en la tabla usuario y por lo tanto son datos erróneos. ...

En el caso de que tengamos múltiples bases de datos llamadas de forma similar, por ejemplo como resultado de tests automatizados que las han ido creando, se puede facilitar el borrado de dichas bases de datos con la siguiente consulta: SELECT 'DROP DATABASE "'||datname||'";' FROM pg_database WHERE datname LIKE 'my_project_test_%'; En el caso de que tuviésemos muchas bases de datos con nombres del estilo my_project_test_1234, esto nos devolvería un resultado tal que así: ?column? ---------------------------------------------- DROP DATABASE "my_project_test_101534"; DROP DATABASE "my_project_test_101622"; DROP DATABASE "my_project_test_101731"; DROP DATABASE "my_project_test_101750"; DROP DATABASE "my_project_test_101889"; DROP DATABASE "my_project_test_102287"; DROP DATABASE "my_project_test_102299"; DROP DATABASE "my_project_test_102335"; DROP DATABASE "my_project_test_102408"; DROP DATABASE "my_project_test_102453"; DROP DATABASE "my_project_test_103004"; DROP DATABASE "my_project_test_103091"; DROP DATABASE "my_project_test_104146"; DROP DATABASE "my_project_test_108842"; DROP DATABASE "my_project_test_108865"; DROP DATABASE "my_project_test_109633"; (16 rows) Entonces borrarlas todas sería muy fácil: basta con copiar ese bloque de consultas y pegarlo en la misma shell psql. ...

Obtener procesos en el backend de PostgreSQL Los procesos del backend de una base de datos PostgreSQL se encuentran detallados en la tabla del sistema pg_stat_activity. postgresql=> \d pg_stat_activity View "pg_catalog.pg_stat_activity" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- datid | oid | | | datname | name | | | pid | integer | | | leader_pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | xact_start | timestamp with time zone | | | query_start | timestamp with time zone | | | state_change | timestamp with time zone | | | wait_event_type | text | | | wait_event | text | | | state | text | | | backend_xid | xid | | | backend_xmin | xid | | | query | text | | | backend_type | text | | | Así por ejemplo si quisiéramos ver un detalle de los procesos activos en una determinada base de datos podríamos hacer la siguiente consulta: ...

La siguiente consulta devuelve elementos duplicados en table cuyos valores para las columnas column_1 y column_2 son idénticos: SELECT column_1, column_2, COUNT(*) FROM my_table GROUP BY column_1, column_2 HAVING count(*) > 1; Usando esta consulta, podríamos borrar todos los elementos duplicados de forma automática mediante esta otra consulta: DELETE FROM my_table AS main USING ( SELECT column_1, column_2, COUNT(*) FROM my_table GROUP BY (column_1, column_2) HAVING COUNT(*) > 1 ) AS duplicates WHERE main.column_1 = duplicates.column_1 AND main.column_2 = duplicates.column_2; SQLAlchemy Esta misma consulta se puede hacer mediante SQLAlchemy de la siguiente forma: session.execute( select( my_table.column_1, my_table.column_2, func.count(my_table.id).label("count") ).from(my_table).group_by(my_table.column_1).having( func.count(my_table.id) ).all()

Utilizando el siguiente comando podemos volcar los resultados de una consulta en PostgreSQL a un fichero CSV sin salir de la consola (Bash, Zsh…): psql -h [URL] -u [USUARIO] -d [NOMBRE] -c "COPY (SELECT * FROM tabla_bd) TO STDOUT WITH CSV HEADER DELIMITER ',';" > resultado.csv

Crear nuevo usuario CREATE USER <user> WITH PASSWORD '<password>'; Asignar privilegios al usuario GRANT ALL PRIVILEGES ON DATABASE <database> TO <user>; Nótese que esta operación no otorga permisos al usuario para crear nuevas bases de datos. Dar permiso al usuario para crear nuevas bases de datos ALTER USER <user> CREATEDB; Dar permisos de superusuario Podemos dar todos los permisos de gestión a un usuario normal con el comando: ALTER ROLE <user> SUPERUSER; Esto nos permite ejecutar diversos comandos, más allá de crear y utilizar bases de datos, con un usuario “normal”, como por ejemplo añadir extensiones a una base de datos existente: ...