SQL Inspection Reference (M0)

Standard set of read-only queries used to check the state of a PostgreSQL/PostGIS database before trusting any assumption about it — schemas present, tables that exist, column types, geometry/SRID, row counts, and spatial indexes. Run this pattern (“M0”) at the start of any milestone, before writing analytical SQL against a table, so decisions are based on the live database rather than on what a doc says it should look like (this project has already found cases where docs and live state diverged — see docs/methodology.md, raw OSM CRS note).

Also useful as interview prep: each query maps to a specific “how do you check X in Postgres” question.

1. List non-system schemas

What schemas actually exist in this database, excluding Postgres internals.

SELECT
    schema_name
FROM
    information_schema.schemata
WHERE
    schema_name NOT IN ('pg_catalog', 'information_schema')
    AND schema_name NOT LIKE 'pg_toast%'
    AND schema_name NOT LIKE 'pg_temp%';

2. List tables in specific schemas

Confirms which tables exist in a given schema (or set of schemas) — useful to check a schema is empty before building into it, or to see what a prior step actually created.

SELECT
    table_schema,
    table_name
FROM
    information_schema.tables
WHERE
    table_schema IN ('raw', 'processed', 'analysis')
    AND table_type = 'BASE TABLE'
ORDER BY
    table_schema,
    table_name;

table_type = 'BASE TABLE' excludes views — drop that filter if you want views included too.

3. Inspect columns and types for one table

The information_schema.columns equivalent of \d <table> — data type, underlying UDT name (useful for spotting geometry columns, which show as USER-DEFINED), and nullability.

SELECT
    column_name AS "Name",
    data_type AS "Type",
    udt_name AS "UDT",
    is_nullable AS "Nullable"
FROM
    information_schema.columns
WHERE
    table_schema = 'PLACEHOLDER_SCHEMA'
    AND table_name = 'PLACEHOLDER_TABLE'
ORDER BY
    ordinal_position;

Order by ordinal_position for the table’s real column order; ordering by data_type (as used during this project’s M0 pass) groups similar columns together instead, which can be easier to scan when checking many tables at once.

4. Geometry column type and SRID

The PostGIS-specific check: geometry type, SRID, and dimensionality for every spatial table in the database. This is the fastest way to confirm a table is in the CRS you think it’s in — never trust a dataset report’s stated CRS without checking this directly.

SELECT
    f_table_schema AS "Schema",
    f_table_name AS "Table",
    f_geometry_column AS "Geometry",
    type AS "Type",
    srid,
    coord_dimension AS "Dimension"
FROM
    public.geometry_columns
ORDER BY
    f_table_schema,
    f_table_name;

Note the geometry column name isn’t always geom — check it here rather than assuming (this project’s processed.stations uses geometry, while every other processed table uses geom).

5. Row count for one table

Exact count, not an estimate. Slow on very large tables (millions of rows) — for a fast approximation instead, query pg_stat_user_tables.n_live_tup.

SELECT COUNT(*) AS row_count
FROM "PLACEHOLDER_SCHEMA"."PLACEHOLDER_TABLE";

6. Spatial (GiST) indexes present in a schema

Confirms whether a geometry column already has a GiST index — required before running ST_Intersects/ST_Intersection against a large table, or you get a sequential scan. Don’t assume an index exists just because a column is typed geometry; indexes are created explicitly.

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'PLACEHOLDER_SCHEMA'
    AND indexdef ILIKE '%gist%';

Why this matters (interview framing)

• information_schema vs. pg_catalog: information_schema is the SQL-standard, cross-database way to inspect metadata (schemas, tables, columns); pg_indexes and geometry_columns are Postgres/PostGIS-specific views layered on top for things the SQL standard doesn’t cover (indexes, spatial reference systems).
• Why check before you build: catches drift between documentation and live state early — cheaper to find a wrong assumption in a SELECT than after building an hour of analysis on top of it.
• Why GiST specifically: PostGIS geometry columns need a spatial index (GiST, or SP-GiST in some cases) to be queried efficiently — a plain B-tree index (Postgres’s default) doesn’t understand bounding-box containment/overlap, which is what spatial predicates like ST_Intersects rely on.
• Exact vs. approximate row counts: COUNT(*) is always correct but requires a full scan; pg_stat_user_tables is instant but reflects the last ANALYZE/VACUUM, so it can drift from the true count on a frequently-changing table.