PostgreSQL Table Admin

Published on July 23, 2025

Below are some useful commands for administering your database tables.

Drop a table

If a table no longer has any value in your project, drop it

DROP TABLE IF EXISTS table_name
;

Truncate / clear all data.

Remove all data but keep its schema intact

TRUNCATE TABLE your_table_name
;

The above command will work unless there are foreign key constraints. For PostgreSQL cascade the truncation to the related tables.

TRUNCATE TABLE your_table_name CASCADE
;

If a table has an automatic number sequence, the truncate process will not reset the sequence. Use the below command to reset the sequence back to “1”.

TRUNCATE your_table_name RESTART IDENTITY
;

Disk space usage

This command gives you the tablename and the associated filesystem space usage.

SELECT 
  schemaname || '.' || relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10
;
Output… 
           table_name            | total_size | table_size | index_size
---------------------------------+------------+------------+------------
 public.dispatch_scada           | 6228 MB    | 4198 MB    | 2030 MB
 public.actual_generation        | 661 MB     | 440 MB     | 221 MB
 public.dispatch_price           | 223 MB     | 157 MB     | 66 MB
 public.tradingis_price          | 133 MB     | 133 MB     | 72 kB
 public.tradingis_interconnector | 90 MB      | 90 MB      | 56 kB
 public.bom_weather_metrics      | 52 MB      | 38 MB      | 14 MB
 public.rooftop_pv_actual        | 35 MB      | 19 MB      | 17 MB
 public.stock_prices             | 18 MB      | 13 MB      | 4600 kB
 public.market_notice            | 1192 kB    | 1104 kB    | 88 kB
 public.auction_units            | 384 kB     | 264 kB     | 120 kB
(10 rows)

Clone a table

These commands clone an existing table called “asx_data”and give it a new name called “asx200_data”. The new table is empty as no data is copied.

-- Environment setup; find "fin" database in public schema
SET search_path TO fin, public;

-- Step 1: Safety step; drop target table if it exists (safe to re-run)
DROP TABLE IF EXISTS asx200_data;

-- Step 2: Create asx200_data with identical structure to asx_data
CREATE TABLE asx200_data (LIKE asx_data INCLUDING ALL);

Add a column

This command adds a column named “gics_ig” to the “asx_metadata” database

ALTER TABLE asx_metadata
ADD COLUMN gics_ig TEXT
;

Create a table

This creates a table called “bme280_env_sensor” for capturing IOT environment data using “TIMESTAMPTZ” for timestamp with time zone context.

CREATE TABLE bme280_env_sensor (
    id INTEGER NOT NULL,
    timestamp TIMESTAMPTZ,
    temperature REAL,
    pressure INTEGER,
    altitude REAL,
    humidity REAL,
    PRIMARY KEY (id)
);

Dump schema

Use this command if you want to copy a DB table but with no data. Its creates and empty skeleton SQL file that can be applied to a new database table.

$ pg_dump -U your_user -s your_db > dump_schema.sql

Create empty DB on new server

This creates a new database where DB schemas and tables can be placed.

$ createdb -U your_user nem_db

Apply schema

Apply the previously dumped schema to the destination database

$ psql -U your_user -d nem_db -f /<path>/dump_schema.sql

Previous

Next