PostgreSQL如何从CSV文件第9行开始导入数据至指定表？

Got it, let's walk through exactly how to get your CSV data into the trader.weather table, skipping those first 8 lines of irrelevant text. I'll cover the two most common methods depending on where your CSV file is stored.

Method 1: Use \copy (Local File, No Superuser Required) #

This is the go-to if your CSV is on your local machine (not the PostgreSQL server). It works directly in the psql command-line tool and doesn't require superuser privileges.

Run this command in psql, replacing the file path with your actual CSV location:

\copy trader.weather (station, "timestamp", temp, wind) FROM '/your/local/path/weather_data.csv' WITH (
    FORMAT csv,
    SKIP 8,
    ENCODING 'UTF8'
);

Quick breakdown of key options: #

• (station, "timestamp", temp, wind): Explicitly defines the column order to match your CSV. We double-quote timestamp because it's a reserved keyword in PostgreSQL.
• SKIP 8: Tells PostgreSQL to ignore the first 8 lines of the file, so we start importing from line 9 exactly as you need.
• ENCODING 'UTF8': Adjust this if your CSV uses a different encoding (e.g., 'GBK' for Chinese characters).

Method 2: Use Server-Side COPY (File on PostgreSQL Server) #

If your CSV is stored directly on the PostgreSQL server's filesystem, use the server-side COPY command. You'll need superuser access for this, and the file must be readable by the postgres system user.

COPY trader.weather (station, "timestamp", temp, wind) FROM '/server/path/weather_data.csv' WITH (
    FORMAT csv,
    SKIP 8,
    ENCODING 'UTF8'
);

Handling Timestamp Format Mismatches #

If your CSV's timestamp format doesn't match PostgreSQL's default timestamp with time zone format (e.g., it uses MM/DD/YYYY HH:MM instead of YYYY-MM-DD HH:MM:SS+TZ), use a temporary table to convert the data first:

1. Create a temporary table to hold raw CSV data:

CREATE TEMP TABLE temp_weather (
    station text,
    raw_timestamp text,
    temp numeric(6,2),
    wind numeric(6,2)
);

2. Import the CSV into the temporary table:

\copy temp_weather FROM '/your/local/path/weather_data.csv' WITH (FORMAT csv, SKIP 8);

3. Convert the timestamp and insert into your target table:

INSERT INTO trader.weather (station, "timestamp", temp, wind)
SELECT
    station,
    -- Replace the format string with your CSV's actual timestamp format
    TO_TIMESTAMP(raw_timestamp, 'MM/DD/YYYY HH24:MI') AT TIME ZONE 'America/New_York',
    temp,
    wind
FROM temp_weather;

Verify the Import #

After running the import, double-check that the data looks correct with a quick query:

SELECT * FROM trader.weather LIMIT 10;

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