Skip to main content
SimilarExport ZIP
100%

List schema and tables from all databases in PostgreSQL cluster

✓ Published0🌍 Public
TThomasG77
Last edited Mar 26, 2023
Created on Mar 26, 2023

This example shows a two-step pipeline that first lists every database in a PostgreSQL cluster and then aggregates all user-defined schemas and tables from each database into a single CSV file. The first `psql` query retrieves database names, excluding `template0`, and writes them to `databases.txt`. A shell loop then runs a second `psql` query against each database, selecting `schemaname` and `tablename` while filtering out system schemas, and appends results with the database name to `all_tables.txt`. The code uses the PostgreSQL command-line client with `-t --csv` flags to output tab-separated CSV data.

AI-generated description

Similar vizzes

Loading thumbnail…

PostgreSQL command line cheatsheet

This example presents a text-based cheat sheet for PostgreSQL command-line tools, rendering a curated list of psql commands, configuration snippets, and handy SQL queries in a plain markdown document. It shows the core syntax for database management tasks, such as connecting to databases, listing tables and views, managing user roles, and adjusting server logging verbosity, alongside query examples for inspecting indexes, active sessions, and table sizes. The visualization relies on static code blocks and inline formatting, with no interactive graphics or JavaScript libraries; instead, it leverages the raw text content from a gist, emphasizing practical API references like `psql` meta-commands and `pg_stat_activity` queries.

AAngieLiang
57% match
Loading thumbnail…

PostgreSQL installation tutorial

This example presents a step-by-step guide for installing PostgreSQL across macOS, Linux, and Windows. It shows the terminal command `psql -U postgres` as the target for verifying a successful setup, then details OS-specific actions, such as using the Postgres.app on macOS, compiling from source via `wget` on Linux, and using the EnterpriseDB installer on Windows. The tutorial also covers checking the `$PATH` variable and verifying the server port with `lsof`.

115Dkatz
53% match
Loading thumbnail…

Gist f2c843666c84195a872efebcc21f1f9f

This PyQGIS script exports the column metadata of an active PostgreSQL layer to a CSV file. It shows the structure of every column, including data type, nullability, precision, defaults, and comments from the `information_schema.columns` view. Working within QGIS, it uses `QgsProviderRegistry` to create a PostgreSQL connection, executes the query via `executeSql`, sanitizes null `QVariant` values, and writes the results with Python’s `csv` module. The final CSV file is then added back to the map as a vector layer.

TThomasG77
53% match
Loading thumbnail…

Gist 61afda568887fcc603e1f9ef963925ec

This example demonstrates a QGIS Processing model that exports spatial data to a PostgreSQL/PostGIS database. The model chains two algorithms: `qgis:postgisexecuteandloadsql` to run SQL and load the result, followed by `qgis:importintopostgis` to write the output to a table named "out2" in the public schema. It uses the PostgreSQL connection "EPSG demo," sets UTF-8 encoding, and creates a geometry column. The model shows a reusable workflow for automating database ingestion directly from the QGIS Processing framework.

TThomasG77
53% match
Loading thumbnail…

Shell script to import data into Postgres from https://www.nhgis.org. If you use this, you'll need to edit line 14 to include the census data you're importing.

This script sets up a Postgres database to hold U.S. Census block-group data, combining demographic race counts with spatial geometries. It first imports CSV race data into a table, then converts shapefiles to a uniform projection (EPSG:4326) using `ogr2ogr` and appends them via `shp2pgsql`. The code uses PostGIS for geometry support and creates a spatial index on the `geom` column. It relies on command-line tools like `dropdb`, `createdb`, and `psql`, processing files from the NHGIS source archive. The final output enables querying joined attribute and spatial data.

CCaged
50% match
Loading thumbnail…

Close all connections from a Postgres database

This example shows how to forcibly terminate every active connection to a named Postgres database except the current session. The SQL query selects all rows from `pg_stat_activity` and calls `pg_terminate_backend(pid)` for each matching process ID, filtering by `datname = 'database'` and excluding the backend’s own PID. The query returns the full activity listing alongside the termination result for each row. The visualization relies solely on the Postgres system catalog function, with no external charting libraries or rendering pipeline.

LLuisSevillano
48% match
Loading thumbnail…

Connexion à PostgreSQL + histogramme avec ggplot2

This example demonstrates how to create a histogram directly from a PostgreSQL database table using R. The code first establishes a database connection with the `DBI` and `RPostgreSQL` packages, then uses `dplyr` and `dbplyr` to query the table as a remote data frame. The `ggplot2` library renders the histogram via `geom_histogram`, mapping a specified column to the aesthetic. Finally, the connection is explicitly closed with `dbDisconnect`. The code illustrates a workflow for plotting distributed data without loading it entirely into local memory.

RRCura
47% match
Loading thumbnail…

Gist 0c701eddfaa4b93ee172

Two terminal sessions show failed attempts to import OpenStreetMap data into a PostgreSQL database using Osmosis, a Java command-line tool. The visualization captures log output from sequential runs of the `osmosis` command, displaying errors from Java's `java.net.ConnectException` when the database connection is refused. The first attempt fails because the `--truncate-pgsql` task receives an unexpected `port` argument, while the second fails because PostgreSQL is not running. The display highlights the pipeline's reliance on database configuration and connection handling, rendered as plain terminal text.

CCaged
47% match