Useful SQL Queries

This is a list of useful SQL queries against the Carto PostgreSQL database.

List tables and row counts for all tables with at least one row

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY n_live_tup DESC;

All visualizations, sorted by user, privacy, and type

SELECT 
    u.username,
    v.privacy,
    v.type,
    v.name,
    v.display_name
FROM users AS u
JOIN visualizations AS v ON (u.id = v.user_id)
ORDER BY u.username, v.privacy, v.type, v.name;

Usernames and user database names for all users

SELECT username, database_name FROM users;