Exploring tables SQL

Published on July 17, 2025

Some useful SQL for exploring data in tables

List all tables in the current schema

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
;
Example… 
        tablename
-------------------------
 actual_generation
 market_notice
 rooftop_pv_actual
 rooftop_pv_file_log
 dispatch_price
 market_notice_metadata
 stock_prices
 auction_units
 arduino_weather_log
 duid_metadata
 dispatch_scada
 bom_weather_metrics
 dispatch_scada_log_file
 bom_stations_metadata
(14 rows)

Preview a table

SELECT * FROM your_table LIMIT 10
;
Example… 
 id |           timestamp           | temperature | pressure | altitude | humidity
----+-------------------------------+-------------+----------+----------+----------
  5 | 2025-07-13 16:45:08.834644+10 |       14.61 |    99856 |   142.26 |    57.37
  6 | 2025-07-13 16:50:10.578831+10 |       14.37 |    99866 |   142.26 |    60.43
  7 | 2025-07-13 16:55:11.909272+10 |       14.21 |    99858 |   142.26 |    61.03
  8 | 2025-07-13 17:00:03.577263+10 |       14.05 |    99861 |   142.26 |    62.33
  9 | 2025-07-13 17:05:04.887211+10 |       13.89 |    99871 |   142.26 |    62.37
 10 | 2025-07-13 17:10:06.187607+10 |       13.99 |    99851 |   142.26 |    61.68
 11 | 2025-07-13 17:15:07.503215+10 |       13.97 |    99862 |   142.26 |    60.34
 12 | 2025-07-13 17:20:08.782364+10 |       14.03 |    99882 |   142.26 |     59.8
 13 | 2025-07-13 17:25:10.17975+10  |       14.15 |    99902 |   133.84 |    59.43
 14 | 2025-07-13 17:30:11.375944+10 |       14.13 |    99880 |   142.26 |    59.56
(10 rows)

Count rows in table

SELECT COUNT(*) FROM your_table
;
Example… 
 count
-------
  1098
(1 row)

Show column names, types and nullability

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table_name'
;
Example… 
           column_name            |          data_type          | is_nullable
----------------------------------+-----------------------------+-------------
 net_payment_per_unit             | numeric                     | YES
 accumulated_net_payment          | numeric                     | YES
 accumulated_net_payment_per_unit | numeric                     | YES
 contract_year                    | integer                     | NO
 end_date                         | timestamp without time zone | YES
 week_no                          | integer                     | NO
 residue_year                     | integer                     | YES
 quarter                          | integer                     | YES
 bill_run_no                      | integer                     | NO
 start_date                       | timestamp without time zone | YES
 purchased_units                  | integer                     | YES
 total_surplus                    | numeric                     | YES
 distributed_surplus              | numeric                     | YES
 auction_fees                     | numeric                     | YES
 net_payment                      | numeric                     | YES
 source_file                      | text                        | YES
 bill_run_type                    | text                        | YES
 interconnector_id                | text                        | NO
 from_region_id                   | text                        | NO
(19 rows)

Search for column name across all tables

SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name ILIKE '%search_word%'
;
Example… 
   table_name   |       column_name
----------------+-------------------------
 dispatch_price | pre_ap_energy_price
 dispatch_price | pre_ap_raise6sec_price
 dispatch_price | pre_ap_raise60sec_price
 dispatch_price | pre_ap_raise5min_price
 dispatch_price | pre_ap_raisereg_price
 dispatch_price | pre_ap_lower6sec_price
 dispatch_price | pre_ap_lower60sec_price
 dispatch_price | pre_ap_lower5min_price
 dispatch_price | pre_ap_lowerreg_price
 dispatch_price | dispatchprices_pre_ap
(10 rows)

Previous

Next