Export from to CSV, JSON or XML

Posted by Kyle Hankinson August 9, 2023

export to CSV, JSON or XML

You can use to export a table or view directly, or to export the results of a result set. Results can be exported to CSV, JSON or XML.

To export from a table or view, find the target in the database tree and right click on it. Choose 'Export as', then pick from either CSV, JSON or XML from one of the submenus.

Similar, when exporting from a results set either right click (or single click the arrow at the top left of resutls) and choose 'Export result set as' and pick from one of the options.

Video example:

Tags: MySQL Feature PostgreSQL Microsoft SQL Server SQLite Snowflake

Counting tables rows in a PostgreSQL database

Posted by Kyle Hankinson August 14, 2020

Overview

Need to find out how many rows, or how much data your PostgreSQL tables are consuming? Possibly, looking to find out what the largest tables in the database are? There are a couple of options available depending on your needs.

Single Table

Finding out how many rows are in a single table is the easiest task. Simply run the query:

SELECT COUNT(1) FROM <TABLE>

You will get a single row result which contains the number of rows:from the specified table.

+----------+
| count(1) |
+----------+
| 7        |
+----------+

Multiple Tables

Counting rows from multiple tables at once is a bit more difficult. You can get an estimate by using the following query:

SELECT
    schemaname as table_schema, relname as table_name, n_live_tup as row_count
FROM
    pg_stat_user_tables
ORDER BY
    n_live_tup DESC;

A more accurate way to get exact row counts courtesy of stackoverflow would be:

select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t order by 3 DESC

Using this second method will take longer and be more likely to slow-down other database operations, however the count will be more accurate.

Size on disk

Sometimes row count might not be exactly what you are looking for. If you need to find out how large a table is in bytes, the pg_relation_size method will come to your help. Finding the size for all tables would be:

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
 FROM
    pg_tables
ORDER BY 1, 2

Which will give the schema, table and pretty printed size for each table.

Tags: PostgreSQL

Killing/cancelling a long running Postgres query

Posted by Kyle Hankinson August 12, 2020

Finding the PID

The first thing you will need to do in order to kill or cancel a PostgreSQL query is to find the PID. This can be found by running the following query:

SELECT * FROM pg_stat_activity WHERE state = 'active';

Now that you have the PID, there are two options for killing the query.

Option #1 (graceful):

SELECT pg_cancel_backend(<PID>);

Option #2 (forceful):

SELECT pg_terminate_backend(<PID>);

Generally, Option #1 should be used as it gracefully terminates the query. Sometimes, however the query still continues to run for a long period of time even after being gracefull terminated. This is when Option #1 should be used.

Terminate all queries

If you want to terminate all running queries, the following statement can be executed:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and pid <> pg_backend_pid();

The above statement will kill all active queries and should only be used in special situations.