Pandas df.to_sql仅插入hourly表却同时写入margin表问题排查

Great question! Let’s get to the bottom of this—this issue is rooted in PostgreSQL’s table inheritance behavior or database-side configurations, not your Python code. Here’s the breakdown:

First, PostgreSQL Inheritance 101 #

When you create hourly as a child table inheriting from margin (with CREATE TABLE hourly () INHERITS (margin);), the default behavior is:

• Data inserted directly into hourly only lives in the hourly table—it does NOT get written to the parent margin table automatically.
• However, when you run SELECT * FROM margin;, PostgreSQL will by default include rows from all child tables (like hourly) in the result. This is a common gotcha that makes it look like data is in margin, but it’s actually just being pulled from the child. To see only data stored directly in margin, use SELECT * FROM ONLY margin;.

Likely Causes for Your Actual Data Duplication #

If you’ve confirmed that data is truly being written to margin (not just appearing in queries due to inheritance), here are the most probable reasons:

1. You Have a Rule or Trigger on the hourly Table #

PostgreSQL lets you define rules or triggers that override or extend default DML behavior. For example, someone might have created a rule like:

CREATE RULE insert_to_margin AS ON INSERT TO hourly
DO ALSO INSERT INTO margin VALUES (NEW.*);

Or a trigger function that copies rows to margin whenever hourly gets new data. These database-side objects will execute automatically when you insert to hourly, even if your Python code only targets hourly.

To check for this:

• Run these queries in psql or your PostgreSQL client:

  -- Check for rules on hourly
  SELECT * FROM pg_rules WHERE tablename = 'hourly';
  -- Check for triggers on hourly
  SELECT * FROM pg_trigger WHERE tgrelid = 'hourly'::regclass;
  

2. The Inheritance Relationship Is Reversed #

It’s possible you accidentally created margin as a child of hourly instead of the other way around. If margin inherits from hourly, inserting to hourly won’t affect margin—but if you mixed up the creation syntax, double-check with:

-- Check parent tables of hourly
SELECT inhparent::regclass FROM pg_inherits WHERE inhrelid = 'hourly'::regclass;
-- Check child tables of margin
SELECT inhrelid::regclass FROM pg_inherits WHERE inhparent = 'margin'::regclass;

3. Misconfiguration in Table Creation #

If you used a custom CREATE TABLE statement for hourly that includes unexpected logic (like a foreign key with ON INSERT CASCADE to margin, or duplicate column definitions that accidentally link to margin), that could also cause this. But this is less likely than the first two causes.

Why Python Code Isn’t the Culprit #

Your df.to_sql(name='hourly', con=engine, if_exists='append', index=False) line only generates and executes INSERT statements targeting the hourly table. Pandas/SQLAlchemy don’t have any built-in logic to automatically write to other tables unless explicitly told to. Since you confirmed you only ran this line, the issue can’t be in your Python code.

Next Steps to Fix It #

1. Verify data location: Run SELECT * FROM ONLY hourly; and SELECT * FROM ONLY margin; to confirm where the data is actually stored.
2. Check for rules/triggers: Use the queries above to find any database-side logic redirecting inserts.
3. Validate inheritance structure: Confirm which table is the parent/child with the pg_inherits queries.

内容的提问来源于stack exchange，提问作者Filip Burgieł