Loading a CSV Into a Staging Table Then Cleaning It in SQL

A CSV that came from people and spreadsheets rarely has clean types: dates in three formats, a dollar sign on the amounts, a name in capitals, a phone number in four styles. Loading it straight into typed columns makes the first bad value stop the whole COPY. The pattern that works is to load it as text, then convert it with SQL, and to put what cannot be converted somewhere you can look at it. This article builds that routine on a four row file and shows the one place my own cleaning went quietly wrong. The wider procedure is in the PostgreSQL loading guide.

The messy file

Four contacts. The first has a padded name, a US date, a currency amount with a thousands comma and a phone number with parentheses. The second is capitalized differently and has another phone format. The third has a written out date, a literal n/a for the amount and no phone. The fourth has a date that is not a date and an amount with a comma where a decimal point belongs.

id,full_name,joined,spend,phone
1, Ana Silva ,03/14/2025,"$1,200.50",(555) 010-2000
2,BO CHEN,2025-03-15,$15,555.010.2001
3,cy dubois,15 Mar 2025,n/a,
4,Dee Evans,not a date,"$7,00",555-010-2003

Step 1: load everything as text

A staging table has the same columns as the file, all text. Because every value is text, nothing in the file can fail a conversion, and the only way the load fails is by having the wrong number of fields, which is a structural error you want to see immediately.

psql <<'SQL'
DROP TABLE IF EXISTS contacts_stage;
CREATE TABLE contacts_stage (id text, full_name text, joined text, spend text, phone text);
\copy contacts_stage FROM 'contacts-messy.csv' WITH (FORMAT csv, HEADER true)
SELECT * FROM contacts_stage ORDER BY id;
SQL

DROP TABLE
CREATE TABLE
COPY 4
 id |  full_name  |   joined    |   spend   |     phone      
----+-------------+-------------+-----------+----------------
 1  |  Ana Silva  | 03/14/2025  | $1,200.50 | (555) 010-2000
 2  | BO CHEN     | 2025-03-15  | $15       | 555.010.2001
 3  | cy dubois   | 15 Mar 2025 | n/a       | 
 4  | Dee Evans   | not a date  | $7,00     | 555-010-2003
(4 rows)

The staging table shows the file as it is, spaces and all. At this stage I can query it: which dates look like what, how many amounts have a dollar sign, which rows have a phone number. That inspection is half of the value of the pattern. A real load would use an UNLOGGED table, as the benchmark in the guide shows, because staging data can be rebuilt from the file.

Step 2: test values without raising errors

The conversion can fail row by row, and you want to know which rows without the query dying on the first. Since PostgreSQL 16, pg_input_is_valid answers that:

psql -qAt -c "SELECT pg_input_is_valid('not a date', 'date'), pg_input_is_valid('2025-03-15', 'date'), pg_input_is_valid('7,00', 'numeric')"
psql -c "SELECT * FROM pg_input_error_info('2025-13-45', 'date')"

f|t|f
                     message                      | detail |                       hint                        | sql_error_code 
--------------------------------------------------+--------+---------------------------------------------------+----------------
 date/time field value out of range: "2025-13-45" |        | Perhaps you need a different "DateStyle" setting. | 22008
(1 row)

The first query returned f|t|f: not a date is not a valid date, 2025-03-15 is, and 7,00 is not a valid numeric. The second shows pg_input_error_info, which returns the error message, detail, hint and SQL error code as a row for a value that fails, here date/time field value out of range: "2025-13-45" with code 22008. Both functions are in the system information functions section of the manual. They test a string against a type without a cast, which is what makes the reject query possible: a cast would raise an error and abort the statement.

Step 3: parse what is parseable

The dates come in three formats, and no single format string handles all of them, so I wrote a small SQL function that recognizes each by a pattern and parses with to_date, returning NULL for anything else. The same script builds the typed table, a reject table with an extra why column, and the two inserts:

-- Turn the all-text staging table into typed rows, and keep what cannot be converted.
CREATE OR REPLACE FUNCTION parse_joined(t text) RETURNS date LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN t ~ '^\d{4}-\d{2}-\d{2}$'      THEN to_date(t, 'YYYY-MM-DD')
    WHEN t ~ '^\d{2}/\d{2}/\d{4}$'      THEN to_date(t, 'MM/DD/YYYY')
    WHEN t ~ '^\d{1,2} [A-Za-z]{3} \d{4}$' THEN to_date(t, 'DD Mon YYYY')
  END
$$;

DROP TABLE IF EXISTS contacts, contacts_reject;
CREATE TABLE contacts (id int PRIMARY KEY, full_name text, joined date, spend numeric(10,2), phone text);
CREATE TABLE contacts_reject AS SELECT *, ''::text AS why FROM contacts_stage LIMIT 0;

INSERT INTO contacts_reject
SELECT s.*, concat_ws('; ',
         CASE WHEN parse_joined(trim(joined)) IS NULL THEN 'unreadable date' END,
         CASE WHEN NULLIF(trim(spend), 'n/a') IS NOT NULL
               AND NOT pg_input_is_valid(replace(regexp_replace(trim(spend), '[$ ]', '', 'g'), ',', ''), 'numeric')
              THEN 'unreadable amount' END)
FROM contacts_stage s
WHERE parse_joined(trim(joined)) IS NULL
   OR (NULLIF(trim(spend), 'n/a') IS NOT NULL
       AND NOT pg_input_is_valid(replace(regexp_replace(trim(spend), '[$ ]', '', 'g'), ',', ''), 'numeric'));

INSERT INTO contacts
SELECT id::int,
       initcap(trim(full_name)),
       parse_joined(trim(joined)),
       NULLIF(replace(regexp_replace(trim(spend), '[$ ]', '', 'g'), ',', ''), 'n/a')::numeric,
       NULLIF(regexp_replace(phone, '\D', '', 'g'), '')
FROM contacts_stage s
WHERE id NOT IN (SELECT id FROM contacts_reject);

The function checks YYYY-MM-DD, then MM/DD/YYYY, then DD Mon YYYY. The order matters, and so does the choice of MM/DD over DD/MM for the slash form. If your source can send either, a pattern cannot tell 03/04/2025 apart, and the mixed date formats article shows what to do about that. The amount cleaning removes dollar signs and spaces, deletes commas, and treats n/a as NULL. The phone column keeps digits only. Names are trimmed and given initial capitals.

I ran the script and looked at both tables:

psql -q -f clean-contacts.sql && psql -c "SELECT * FROM contacts ORDER BY id" -c "SELECT id, full_name, joined, spend, why FROM contacts_reject"

 id | full_name |   joined   |  spend  |   phone    
----+-----------+------------+---------+------------
  1 | Ana Silva | 2025-03-14 | 1200.50 | 5550102000
  2 | Bo Chen   | 2025-03-15 |   15.00 | 5550102001
  3 | Cy Dubois | 2025-03-15 |         | 
(3 rows)

 id | full_name |   joined   | spend |       why       
----+-----------+------------+-------+-----------------
 4  | Dee Evans | not a date | $7,00 | unreadable date
(1 row)

Three rows went to contacts, with the names tidied, the three date styles all resolved, $1,200.50 as 1200.50, $15 as 15.00, and the third row's n/a and missing phone as NULL. One row went to the reject table with the reason unreadable date: Dee Evans with the date not a date.

The part that went quietly wrong

Look at the rejected row's amount: $7,00. My cleaning deletes every comma, so it turns $7,00 into 700, which is a valid number, and the check for an unreadable amount did not fire. A European writer meant seven. If that row had had a good date, it would have been inserted with an amount of 700, a hundredfold error that no constraint would catch.

That is the real hazard of cleaning in SQL, and it is not the date. A rule that is correct for the common case, a comma as a thousands separator, silently misreads the rare case. There are two defenses. Make the rule strict: a comma is a thousands separator only if it is followed by exactly three digits, and a comma followed by one or two digits at the end of the string is a decimal comma and deserves a reject, or a conversion if you know the source's locale. And measure after the fact: compare totals with the source where you can, and look at the largest values.

-- A comma that is a thousands separator is followed by exactly three digits
WHEN spend ~ ',\d{1,2}$' THEN 'decimal comma, check the locale'

I would add that condition to the reject query and rerun. The staging pattern makes this kind of improvement cheap: nothing is lost, the original text is still in the staging table, and the cleaning is a script you rerun.

Keep the reject table

The rows that failed are not garbage. They are a list of exactly what the data provider got wrong, with a reason, and sending it back is how the next file gets better. Keep it as a table or export it with \copy (SELECT * FROM contacts_reject) TO 'rejects.csv' CSV HEADER. A routine that quietly drops bad rows has the same effect on the data as an ON_ERROR ignore load without a log, and the article on skipping bad rows covers the built in way to do it when a staging table is too heavy.

How to tell it worked

Three counts must add up: rows in staging equals rows in the typed table plus rows in the reject table. Here that is 4 = 3 + 1. Then compare a total of a numeric column between the staging table, converted with the same rule in a query, and the typed table. And look at the extremes: min and max of each converted column, which is where a misread number shows up.

SELECT (SELECT count(*) FROM contacts_stage) AS staged,
       (SELECT count(*) FROM contacts)       AS typed,
       (SELECT count(*) FROM contacts_reject) AS rejected;

When a staging table is overkill

For a clean, typed file from a system you trust, a direct COPY into the final table is simpler and faster, and its all or nothing behavior is the check. For a file you will load once to explore, DuckDB can read it with type inference and no staging, as in the DuckDB guide. A staging table also costs a second write of the data, so for files in the hundreds of millions of rows it is worth measuring, as the 2,000,000 row test in the partial columns article did: the staging route took 3.1 s against 0.82 s for a pre-filter, and a conversion step adds more. The pattern earns its cost when the cleaning is complicated or the rejects matter, and for a file that arrives weekly from a human, they almost always do.