Skip to main content

Postgres Queries

This page covers the PostgreSQL-side checks and queries used most often when validating a MangoGridStudio unit commitment run.

PostgreSQL Session Setup​

If Row-Level Security is enabled, set the namespace for the tenant before you query any uc_* tables:

SET app.namespace = 'org:your-namespace';

SELECT job_id, status, objective_value, solve_time_seconds, mip_gap
FROM uc_jobs
WHERE job_id = 'your-job-id';

Input Tables​

Persisted unit commitment inputs live in:

  • uc_networks
  • uc_generators
  • uc_buses
  • uc_lines
  • uc_load_profiles
  • uc_renewable_profiles
  • uc_reserve_requirements

Pull Network-Level Input Summary​

SELECT
    n.job_id,
    n.network_name,
    n.iso_id,
    n.date_start,
    n.date_end,
    n.horizon_hours,
    COUNT(DISTINCT g.gen_id) AS generator_count,
    COUNT(DISTINCT b.bus_id) AS bus_count,
    COUNT(DISTINCT l.line_id) AS line_count
FROM uc_networks n
LEFT JOIN uc_generators g ON g.job_id = n.job_id
LEFT JOIN uc_buses b ON b.job_id = n.job_id
LEFT JOIN uc_lines l ON l.job_id = n.job_id
WHERE n.job_id = 'your-job-id'
GROUP BY
    n.job_id,
    n.network_name,
    n.iso_id,
    n.date_start,
    n.date_end,
    n.horizon_hours;

Pull Hourly Load Inputs​

SELECT job_id, bus_id, hour, load_mw
FROM uc_load_profiles
WHERE job_id = 'your-job-id'
ORDER BY hour, bus_id;

Output Tables​

Persisted unit commitment outputs live in:

  • uc_commitments
  • uc_dispatches
  • uc_lmps
  • uc_system_lambdas
  • uc_line_shadows
  • uc_end_of_day_states
  • uc_backtest_day_results
  • uc_solve_artifacts
  • uc_warnings

Pull Commitment and Dispatch Outputs​

SELECT
    d.job_id,
    d.hour,
    d.gen_id,
    c.is_on,
    c.startup,
    c.startup_tier,
    c.shutdown,
    d.dispatch_mw
FROM uc_dispatches d
JOIN uc_commitments c
    ON c.job_id = d.job_id
   AND c.gen_id = d.gen_id
   AND c.hour = d.hour
WHERE d.job_id = 'your-job-id'
ORDER BY d.hour, d.gen_id;

Pull LMPs and System Lambda​

SELECT
    l.job_id,
    l.hour,
    l.bus_id,
    l.lmp_total,
    l.lmp_energy,
    l.lmp_congestion,
    l.lmp_loss,
    s.system_lambda
FROM uc_lmps l
LEFT JOIN uc_system_lambdas s
    ON s.job_id = l.job_id
   AND s.hour = l.hour
WHERE l.job_id = 'your-job-id'
ORDER BY l.hour, l.bus_id;

Pull LP and Solver Log Paths​

SELECT job_id, instance_id, lp_file_path, solver_log_path, artifact_prefix, created_at
FROM uc_solve_artifacts
WHERE job_id = 'your-job-id';