如何在本地PostgreSQL中复现Heroku Dataclips及添加现有数据？

Great questions—let's break this down clearly, since relying on Dataclips for production views is such a handy workflow when you're normalizing schemas and don't want to overthink raw table readability. First, let's confirm your final question, then dive into the overview and step-by-step actions.

Quick Confirmation: Heroku Dataclips vs. PostgreSQL Views #

Heroku Dataclips are not exactly PostgreSQL database views, but they serve a nearly identical purpose for querying data. Dataclips are saved SQL queries hosted on the Heroku platform (not stored as permanent objects inside your PostgreSQL database). That said, you can easily replicate their functionality using actual PostgreSQL views locally, which is exactly what we'll cover below.

高层概述 #

Core Concepts #

• Importing existing Dataclips to local: The goal is to take the SQL logic from your Heroku-hosted Dataclips and replicate it in your local PostgreSQL database. The most durable way to do this is by creating permanent views that mirror the Dataclip's output.
• Recreating the Dataclips workflow locally: You want to avoid rewriting queries constantly while developing, so we'll cover two approaches:
  1. Permanent database views (for queries you use daily/consistently)
  2. Reusable query scripts/snippets (for ad-hoc or frequently tweaked queries)
     This lets you query your local test data—whether it's a pg:pull copy of production or your own test dataset—using the exact same logic you trust in production.

具体操作方法及示例 #

1. Adding Existing Heroku Dataclips to Your Local PostgreSQL Database #

Follow these steps to turn your Dataclips into persistent local database objects:

• Step 1: Extract the Dataclip's SQL
  Log into your Heroku Dashboard, navigate to the Dataclips section, open the Dataclip you want to replicate, and copy the full SQL query text (make sure to grab CTEs, joins, filters—everything that defines the Dataclip's output).
• Step 2: Connect to your local PostgreSQL database
  Use psql in your terminal (replace local_dev_db with your actual database name):

  psql -d local_dev_db
  

  Or use a GUI tool like pgAdmin if you prefer a visual interface.
• Step 3: Create a local view
  Run the CREATE VIEW command to turn the Dataclip's SQL into a permanent view. For example:

  CREATE VIEW customer_order_summary AS
  -- Paste your Dataclip's SQL here
  SELECT
    customers.id AS customer_id,
    customers.email,
    COUNT(orders.id) AS total_orders,
    SUM(orders.amount) AS total_spent
  FROM customers
  LEFT JOIN orders ON customers.id = orders.customer_id
  GROUP BY customers.id, customers.email;
  

  If you need to update the view later (if the Dataclip changes), use CREATE OR REPLACE VIEW instead of CREATE VIEW.
• Step 4: Verify the view works
  Query the view just like you would a regular table:

  SELECT * FROM customer_order_summary LIMIT 10;
  

  Confirm the output matches what you see in the Heroku Dataclip.

2. A Clean Workflow to Replicate Dataclips Locally #

If permanent views feel overkill (or you have queries you tweak often), use these lighter-weight methods:

方法A: Save Query Scripts for Quick Execution

• Save your Dataclip's SQL into a dedicated file, e.g., scripts/queries/customer_order_summary.sql in your project directory.
• Run it anytime against your local database with this terminal command:

  psql -d local_dev_db -f scripts/queries/customer_order_summary.sql
  

  Perfect for ad-hoc checks or queries you only need occasionally.

方法B: Use psql Custom Commands for Instant Access

• Open your ~/.psqlrc file (create it if it doesn't exist) and add a custom variable for your Dataclip query:

  \set customer_summary 'SELECT customers.id AS customer_id, customers.email, COUNT(orders.id) AS total_orders, SUM(orders.amount) AS total_spent FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id, customers.email;'
  

• Now, whenever you're in psql, just run:

  :customer_summary
  

  The query will execute instantly—just like pulling up a Dataclip in Heroku.

内容的提问来源于stack exchange，提问作者mwal