从Oracle导入表至PostGre的最优方案及自动建列方法问询

Hey there! Let’s tackle your two questions about moving data from Oracle to PostgreSQL— I’ve dealt with this exact setup (read-only Oracle, using SQL Developer + pgAdmin) plenty of times, so I’ve got some practical tips for you.

The best approach depends on how often you need to do this, how much data you’re moving, and whether you need automation. Here are the top options:

• Ad-Hoc Transfers with SQL Developer + pgAdmin (Great for One-Off Queries)
  This is perfect for your current scenario: you run a query in read-only Oracle, export the results, and import into PG. It’s straightforward and uses tools you already have.

• PostgreSQL Foreign Data Wrapper (FDW) for Oracle (Automated/Repeatable Syncs)
  If you need to pull data regularly, set up the oracle_fdw extension in PostgreSQL. This lets you connect directly to your Oracle database from PG, then run a CREATE TABLE AS SELECT * FROM oracle_table; (or your custom query) to auto-create the PG table with all columns and data types mapped correctly. No manual CSV exports needed— super efficient for recurring tasks.

• ETL Tools (For Large-Scale/Complex Migrations)
  If you’re moving dozens of tables, need data transformations, or have strict performance requirements, tools like Talend, Apache Airflow work well. These handle edge cases like data type conversions, incremental syncs, and error handling automatically.

Absolutely— you don’t need to manually create every column! Here’s the step-by-step workflow I use:

1. Export from Oracle SQL Developer
   Run your query, right-click the result set → Export → Choose CSV as the format. Make sure to:

   • Check "Include column headers" (this is key for pgAdmin to auto-detect columns)
   • Set the encoding to UTF-8 (matches PostgreSQL’s default)
   • Save the CSV file to a location you can access from pgAdmin.

2. Import into pgAdmin
   Connect to your PostgreSQL database, right-click the target schema (e.g., public) → Import/Export Data → Select Import from the dropdown.

   • Under "File Options", select your CSV file.
   • Go to the "Options" tab: Check "Header" (since your CSV has column names).
   • Switch to the "Columns" tab: pgAdmin will automatically detect all column names and guess the correct data types (e.g., Oracle’s VARCHAR2 becomes PG’s VARCHAR, NUMBER becomes NUMERIC). You can tweak types here if needed, but the auto-detection is usually spot-on.
   • Click OK— pgAdmin will create the new table and import all your data in one go.

Quick Notes: #

• If your query has special data types (like DATE, CLOB, or TIMESTAMP), double-check the auto-mapped types in pgAdmin to ensure they match what you need.
• For very large datasets (100k+ rows), the FDW method is faster than CSV exports/imports since it avoids writing to disk.

内容的提问来源于stack exchange，提问作者Oh-No