SQL (PostgreSQL, MySQL, SQLite) Module 10 – Changing data: INSERT, UPDATE, DELETE and UPSERT

Bulk loading and exporting CSV

Load CSV files with COPY, LOAD DATA and the sqlite3 shell, check rows in a staging table first, and export query results to CSV in each engine.

Intermediate
20 minutes
Examples run with sql.js 1.14.2 and Python 3.14.8
By MySmartCoPilot

Free: The command decides which computer reads the file · CSV carries text without types or NULL · A staging table catches bad rows before the real table · An export writes a query's rows, on the client or the server. With a pass: 2 in-depth parts and 3 interview answers, about 13 minutes.

What you will learn

Load CSV files with COPY and \copy, LOAD DATA and the sqlite3 shell's .import
Check imported rows in a staging table before copying them into typed tables
Export query results to CSV in PostgreSQL, MySQL and SQLite
Optimise large loads with one transaction and the right order of indexes and checks
Protect file access and exported files from misuse

Before you start

INSERT, defaults and getting generated keys

What you’ll learn · Before you start

What you will learn

Load CSV files with COPY and \copy, LOAD DATA and the sqlite3 shell's .import
Check imported rows in a staging table before copying them into typed tables
Export query results to CSV in PostgreSQL, MySQL and SQLite
Optimise large loads with one transaction and the right order of indexes and checks
Protect file access and exported files from misuse

Before you start

INSERT, defaults and getting generated keys

On this page

A supplier’s file of six products arrives with a price of abc, an empty name and one sku twice, and a loader that trusts it either stops halfway or stores the bad rows. bulk load: Loading the rows of a file into a table with one command, such as PostgreSQL's COPY, MySQL's LOAD DATA or the sqlite3 shell's .import, instead of one INSERT per row. moves the rows of a file into a table with one command, instead of one INSERT per row, and exporting CSV writes the result of a query to a file. PostgreSQL loads with COPY … FROM, which reads a file on the database server, and with psql’s \copy, which reads one on your computer. MySQL loads with LOAD DATA INFILE and LOAD DATA LOCAL INFILE, and the sqlite3 shell with .import --csv. Two habits make bulk loading safe: load into a staging table: A table of plain text columns that a file is loaded into first, so that a query can check every row and report the bad ones with their line before the good ones are copied into the real, typed table. and check the rows there, and do the whole load in one transaction.

The SQL scripts on this page run in SQLite 3.49.1 through sql.js 1.14.2. The loaders work on files, which a web page cannot hold, so recorded programs run them on PostgreSQL 18.6, MySQL 8.4.11 and the sqlite3 shell.

The command decides which computer reads the file

Who reads the file in a bulk load

Text description of the diagram

The diagram shows two ways a CSV file reaches the database server, from top to bottom.

A file products.csv on your computer is read by the client program: psql's \copy, the mysql client's LOAD DATA LOCAL INFILE, or the sqlite3 shell's .import. The client program sends the rows to the database server over its connection. (For SQLite the database is itself a file on your computer, so the shell writes the rows into it directly.)
A file products.csv stored on the database server is read by the server itself, with PostgreSQL's COPY … FROM 'file' or MySQL's LOAD DATA INFILE. The path is then a path on the server, and the server checks that the user may read files there.

Every loader answers one question first: which program opens the file. A client command reads a file on your computer and sends its rows over the connection. A server command makes the database server open a path on its own disk, which is faster for huge files and needs special privileges:

COPY … FROM 'file' makes the PostgreSQL server read a file on its disk; it needs pg_read_server_files.
\copy is a psql command that reads the file on your computer and streams it to the server.
LOAD DATA INFILE makes MySQL read a server file; it needs the FILE privilege and the folder secure_file_priv: MySQL's setting that limits LOAD DATA INFILE and SELECT … INTO OUTFILE to the files of one folder on the database server. allows.
LOAD DATA LOCAL INFILE sends a file from your computer instead, and is switched off by default.
.import --csv is the sqlite3 shell’s loader: it reads a CSV and inserts its rows into a table.
COPY … TO, SELECT … INTO OUTFILE and .mode csv write a query’s result as a CSV file.

Loading a CSV file in each engine
Criterion	PostgreSQL 18	MySQL 8.4	sqlite3 shell
A file on your computer	\copy products FROM … WITH (FORMAT csv, HEADER)	LOAD DATA LOCAL INFILE …, with local_infile on	.import --csv products.csv products
A file on the server	COPY products FROM …, with pg_read_server_files	LOAD DATA INFILE …, with FILE and secure_file_priv	Not applicable: no server
The header line	HEADER skips it; HEADER MATCH checks the names	IGNORE 1 LINES	Used as column names for a new table; --skip 1 otherwise
An empty field	NULL, unless quoted ""	'' for text; for a number, an error, or 0 with LOCAL; \N is NULL	''
A bad value, by default	The whole load stops	Server file: the load stops; LOCAL: a warning	That line is skipped, or stored as text

Read the table by the file’s location first: the first two rows decide the command, the last three what it does with the file’s content.

Checkpoint

Check: Who reads the file

Question 1 of 2 You connect with psql to a PostgreSQL server in the cloud and run COPY products FROM '/home/asha/products.csv' WITH (FORMAT csv, HEADER). The file is on your laptop. What happens?
Choose one answer.

psql sends the file from the laptop to the server It creates an empty table products It loads the file, because COPY always reads on the client It fails: COPY with a file name reads that path on the server, where the file is not, and needs file privileges there

Show the answer: Question 1 of 2

Answer: It fails: COPY with a file name reads that path on the server, where the file is not, and needs file privileges there

COPY with a file name is run by the server process and reads the server's file system, and only roles with the privileges of pg_read_server_files may do it. psql's \copy, with the same options, reads the file on the laptop and streams it to the server.
Question 2 of 2 Which MySQL statement reads a file that sits on the computer where the mysql client runs?
Choose one answer.

LOAD DATA INFILE SELECT … INTO OUTFILE COPY … FROM STDIN LOAD DATA LOCAL INFILE, which is switched off by default

Show the answer: Question 2 of 2

Answer: LOAD DATA LOCAL INFILE, which is switched off by default

MySQL's LOAD DATA reads a server file; with LOCAL the client reads the file and sends its rows, which is why local_infile is off unless both the server and the client allow it.

CSV carries text without types or NULL

RFC 4180 describes the common format: one record per line, fields separated by commas, an optional header line, and fields that contain a comma, a quote or a line break wrapped in double quotes, with every quote inside doubled. Step through one line of the supplier’s file:

Interactive · stage by stage One line of products.csv, field by field Step through the dal line. Each field is text; the last one is empty, and each engine reads that in its own way.

The drawing shows the whole scene; its 5 steps are listed below.

Every step as text

The line in the file. The file holds the line DAL-500G,"Toor dal, 500 g",Pulses,8950, and commas separate its five fields. CSV has no types and no NULL of its own, and its header line is optional.
A plain field. The first field runs to the first comma: DAL-500G, the sku.
A quoted field. The name holds a comma, so the file wraps it in double quotes, and the comma inside belongs to the value. The loader removes the quotes; a quote inside a quoted field is written twice.
Numbers are text. The price arrives as the four characters 8950. It becomes a number only when the database converts it to the column type, which is where a value like abc fails.
The empty field. The brand is empty. PostgreSQL's COPY in CSV format reads it as NULL; MySQL's LOAD DATA stores an empty string in a text column; the sqlite3 shell's .import stores an empty string.

The format has no types, so 14999 is only text until the database converts it, and it has no marker for NULL. Each engine therefore picks its own rule, and the comparison table above shows how far apart they are. The same file can load NULL in PostgreSQL and an empty string in MySQL or SQLite. Agree on UTF-8 with whoever sends the file, and tell the loader too: PostgreSQL’s COPY takes an ENCODING option, and MySQL’s LOAD DATA a CHARACTER SET clause.

Checkpoint

Check: What a CSV line carries

Question 1 of 2 How many fields does the CSV line DAL-500G,"Toor dal, 500 g",Pulses,8950, hold, read as RFC 4180 describes?
Type a number.
Your answer fields

A hint

A comma inside double quotes belongs to the value.

Show the answer: Question 1 of 2

Answer: 5 fields

The quoted name keeps its comma, so it is one field, and the trailing comma ends the fourth field and starts an empty fifth one: sku, name, category, price and an empty brand.
Question 2 of 2 In PostgreSQL's CSV format, what does an unquoted empty field, as in GHEE-1L,,Dairy, load as?
Choose one answer.

An empty string '' An error NULL The column's default

Show the answer: Question 2 of 2

Answer: NULL

CSV has no marker for NULL, so each engine picks a rule. PostgreSQL's COPY reads an unquoted empty field as NULL; MySQL's LOAD DATA reads \N as NULL and an empty text field as an empty string, and the sqlite3 shell's .import stores an empty string.

A staging table catches bad rows before the real table

The sqlite3 shell’s .import --csv products.csv raw_products creates raw_products with one text column per field of the header. The script starts from such a table:

Checking an imported file, then copying the good rows SQL · staging_load.sql

-- What the sqlite3 shell's ".import --csv products.csv raw_products" creates: one TEXT column per
-- header field, every value as text, in file order (rowid 1 is line 2 of the file).
CREATE TABLE raw_products (sku TEXT, name TEXT, category TEXT, price_paise TEXT, brand TEXT);
INSERT INTO raw_products (sku, name, category, price_paise, brand)
VALUES ('RICE-1KG',  'Basmati rice 1 kg', 'Grains',    '14999', 'mart'),
       ('DAL-500G',  'Toor dal, 500 g',   'Pulses',    '8950',  ''),
       ('CHAI-250G', 'Masala chai 250 g', 'Beverages', 'abc',   'mart'),
       ('OIL-1L',    'Coconut oil 1 L',   'Oils',      '21500', ''),
       ('GHEE-1L',   '',                  'Dairy',     '64900', ''),
       ('RICE-1KG',  'Basmati rice 1 kg', 'Grains',    '14999', 'mart');

-- The real table: types and rules.
CREATE TABLE products (
  sku         TEXT    PRIMARY KEY,
  name        TEXT    NOT NULL CHECK (name <> ''),
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL CHECK (price_paise > 0),
  brand       TEXT                                     -- NULL when the file left it empty
) STRICT;

-- 1. Check every row, and keep the line of the file it came from.
CREATE TEMP TABLE checked AS
SELECT rowid + 1 AS line, sku, name, category, price_paise, brand,
       CASE
         WHEN name = '' THEN 'empty name'
         WHEN price_paise = '' OR price_paise GLOB '*[^0-9]*' THEN 'price is not a whole number of paise'
         WHEN rowid > (SELECT min(r.rowid) FROM raw_products AS r WHERE r.sku = raw_products.sku)
           THEN 'sku repeated in the file'
       END AS problem
FROM raw_products;

SELECT line, sku, problem FROM checked WHERE problem IS NOT NULL;

-- 2. Copy the good rows in one transaction, converting text to the real types.
BEGIN;
INSERT INTO products (sku, name, category, price_paise, brand)
SELECT sku, name, category, CAST(price_paise AS INTEGER), nullif(brand, '')
FROM checked
WHERE problem IS NULL;
SELECT changes() AS rows_loaded;
COMMIT;

SELECT sku, price_paise, brand FROM products ORDER BY sku;

Output

┌──────┬───────────┬──────────────────────────────────────┐
│ line │    sku    │               problem                │
├──────┼───────────┼──────────────────────────────────────┤
│ 4    │ CHAI-250G │ price is not a whole number of paise │
│ 6    │ GHEE-1L   │ empty name                           │
│ 7    │ RICE-1KG  │ sku repeated in the file             │
└──────┴───────────┴──────────────────────────────────────┘
┌─────────────┐
│ rows_loaded │
├─────────────┤
│ 3           │
└─────────────┘
┌──────────┬─────────────┬───────┐
│   sku    │ price_paise │ brand │
├──────────┼─────────────┼───────┤
│ DAL-500G │ 8950        │       │
│ OIL-1L   │ 21500       │       │
│ RICE-1KG │ 14999       │ mart  │
└──────────┴─────────────┴───────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < staging_load.sql

The check found three problems, each with the line of the file it came from: a price that is not a number, an empty name and a repeated sku. Step through the load from the file to the two places its rows end up:

Interactive · stage by stage Six products through a staging table Step through the load. Nothing reaches the real table before it has been checked, and the bad rows end up in a list a person can fix.

The drawing shows the whole scene; its 5 steps are listed below.

Every step as text

Import as text. .import --csv products.csv raw_products creates a table with one TEXT column per header field and loads all 6 rows. Text accepts anything, so nothing is refused yet.
Check, with the line number. A query gives each row a problem, or none, and keeps the line of the file it came from: rowid + 1, since line 1 is the header.
Three problems. Line 4 has the price abc, line 6 an empty name, and line 7 repeats the rice sku. They go to the reject list, which a person can act on.
Convert and copy. Inside one transaction, INSERT … SELECT copies the rows without a problem, converting price_paise with CAST and the empty brand to NULL with nullif.
The real table. products, a STRICT table with NOT NULL and CHECK rules, receives the 3 good rows. The bad ones never reached it, so its rules never had to refuse anything.

The three good rows went into the typed products table in one statement, converted with CAST on the way, and nullif(brand, '') turned the empty brand fields into NULL, which the file had no way to say. Nothing reached the real table unchecked, and the problem rows are a report a person can fix. Doing the whole load in one transaction makes it all-or-nothing, and much faster than a commit per row.

Key idea

Load a file into a table of text columns, name every problem with its line, and copy only the good rows into the typed table, converting them on the way, in one transaction.

Checkpoint

Check: Staging and checking rows

Question 1 of 2 The supplier file of the staging example has a header line and 6 products. Its check found 3 problems. How many rows did the INSERT … SELECT copy into products?
Type a number.
Your answer rows

Show the answer: Question 1 of 2

Answer: 3 rows

Of the 6 rows, the price abc, the empty name and the repeated sku had problems, so the INSERT … SELECT copied the other 3, and changes() reported 3.
Question 2 of 2 Why does the staging table give every column the type TEXT, when the real table has an INTEGER price?
Choose one answer.

Because SQLite cannot store integers in a staging table So that every line of the file loads, and the check can report a bad value with its line instead of the load failing To make the load faster Because CAST works only on text

Show the answer: Question 2 of 2

Answer: So that every line of the file loads, and the check can report a bad value with its line instead of the load failing

A typed table would refuse or quietly change a bad value as it loads. A table of text columns takes every line, and a query then names each problem with the line it came from, before anything reaches the real table.

An export writes a query’s rows, on the client or the server

Exporting CSV has the same client and server split. In psql, \copy (SELECT category, count(*) AS products FROM products GROUP BY category ORDER BY category) TO 'report.csv' WITH (FORMAT csv, HEADER) writes the file on your computer, and COPY (…) TO STDOUT prints the same lines. MySQL’s SELECT … INTO OUTFILE writes on the server, into the folder secure_file_priv allows, and refuses to overwrite a file that exists. From your computer, the mysql client’s --batch option prints tab-separated rows with a header line. The sqlite3 shell exports with .headers on, .mode csv and .once report.csv, followed by the query. Here is what PostgreSQL 18.6 prints for the report, and for a list whose names hold a comma:

A query exported as CSV, on PostgreSQL 18.6

pg/export_postgres.py

"""PostgreSQL 18.6: a query's result written as CSV with COPY … TO STDOUT, the lines that psql's \\copy … TO writes
into a file on your computer."""

from pg_server import psql

psql(r"""
CREATE TABLE products (sku text PRIMARY KEY, name text NOT NULL, category text NOT NULL, price_paise int NOT NULL);
INSERT INTO products VALUES
  ('RICE-1KG',  'Basmati rice 1 kg', 'Grains', 14999),
  ('DAL-500G',  'Toor dal, 500 g',   'Pulses',  8950),
  ('OIL-1L',    'Coconut oil 1 L',   'Oils',   21500),
  ('POHA-500G', 'Poha 500 g',        'Grains',  4550);

\echo '-- 1. A report: one line per category, after a header line'
COPY (SELECT category, count(*) AS products FROM products GROUP BY category ORDER BY category)
TO STDOUT WITH (FORMAT csv, HEADER);

\echo '-- 2. A name with a comma is quoted, so it stays one field'
COPY (SELECT sku, name, price_paise FROM products ORDER BY sku) TO STDOUT WITH (FORMAT csv, HEADER);
""")

Output

-- 1. A report: one line per category, after a header line
category,products
Grains,2
Oils,1
Pulses,1
-- 2. A name with a comma is quoted, so it stays one field
sku,name,price_paise
DAL-500G,"Toor dal, 500 g",8950
OIL-1L,Coconut oil 1 L,21500
POHA-500G,Poha 500 g,4550
RICE-1KG,Basmati rice 1 kg,14999

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 export_postgres.py

pg/pg_server.py

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on the
loopback address 127.0.0.1, on a free port chosen for this run, with a password made up for this run (scram-sha-256;
the password is never printed), sends the SQL to psql, then stops the server and deletes the folder. Every run
therefore starts from an empty database and prints the same text. (The server opens no Unix socket: the temporary
folders of these runs are too deep for macOS's 103-byte limit on socket paths.)

The settings keep the output the same on every run: no autovacuum (statistics change only when the SQL runs ANALYZE
or VACUUM), statistics taken from every row instead of a random sample (default_statistics_target = 10000), no
parallel workers, no JIT compilation, and WAL buffers large enough never to fill up during a run. The cluster uses the
C locale, UTF-8 and UTC.

PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6). That folder is in the home
folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded with its
network isolation off (LEARN_VERIFY_NETWORK=allow); the server accepts connections from this computer only. The
output is psql's own, except that the "psql:<stdin>:<line>:" in front of each message is left out and the run's port
number is printed as "<port>". Each call of psql() is a new psql process, so a new connection, on the same server.
"""

import atexit
import os
import pwd
import re
import secrets
import shutil
import socket
import subprocess
import sys

BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

_base = os.path.join(os.path.normpath(os.environ.get("TMPDIR", os.getcwd())), "pg")
_data = os.path.join(_base, "d")
_running = False
_port = ""
_env: dict[str, str] = {}


def _free_port() -> str:
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        s.bind(("127.0.0.1", 0))
        return str(s.getsockname()[1])


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _running, _port, _env
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    password = secrets.token_urlsafe(18)
    pwfile = os.path.join(_base, "pw")
    with open(pwfile, "w") as f:
        f.write(password + "\n")
    _port = _free_port()
    _env = dict(os.environ, PGHOST="127.0.0.1", PGPORT=_port, PGUSER="postgres", PGDATABASE="postgres",
                PGPASSWORD=password, LC_ALL="C", LANG="C", TZ="UTC")
    _quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "scram-sha-256", f"--pwfile={pwfile}",
           "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    os.remove(pwfile)
    options = (
        f"-c listen_addresses=127.0.0.1 -c port={_port} -c unix_socket_directories='' -c fsync=off -c timezone=UTC"
        " -c autovacuum=off -c default_statistics_target=10000 -c max_parallel_workers_per_gather=0 -c jit=off"
        " -c wal_buffers=64MB"
    )
    _quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _running = True
    atexit.register(_stop)


def _stop() -> None:
    global _running
    if _running:
        subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _running = False
    shutil.rmtree(_base, ignore_errors=True)


def _clean(text: str) -> str:
    return WHERE.sub("", text).replace(f"port {_port}", "port <port>")


def psql(sql: str) -> None:
    """Sends a block of SQL to psql and prints what it printed, errors included, in order."""
    if not _running:
        _start()
    run = subprocess.run(
        [os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(_clean(run.stdout))
    sys.stdout.flush()

Each export starts with its header line, and only the dal name, which holds a comma, is wrapped in quotes.

CSV to SQL Converter Turn a CSV file into a CREATE TABLE and INSERT batches, or into a COPY, LOAD DATA or .import script, for your engine. SQLite Online (SQL Playground) Import a CSV file as a table in your browser, check its rows with SQL, and copy the good ones into a typed table.

Checkpoint

Check: Exporting a query as CSV

Question 1 of 2 In psql, which command writes a query's result as a CSV file with a header line on your own computer?
Choose one answer.

COPY (SELECT …) TO 'report.csv' WITH (FORMAT csv, HEADER) \copy (SELECT …) TO 'report.csv' WITH (FORMAT csv, HEADER) SELECT … INTO OUTFILE 'report.csv' .once report.csv

Show the answer: Question 1 of 2

Answer: \copy (SELECT …) TO 'report.csv' WITH (FORMAT csv, HEADER)

Exports have the same client and server split as loads. \copy writes on your computer; COPY … TO a file writes on the server; COPY … TO STDOUT prints the same lines, which is what the PostgreSQL run shows.
Question 2 of 2 In the PostgreSQL export, why is the dal line written as DAL-500G,"Toor dal, 500 g",8950?
Choose one answer.

COPY quotes every text field The name holds a comma, so COPY quotes the field, and a reader of the file sees one field, not two The quotes come from the data in the table Because the name is longer than the others

Show the answer: Question 2 of 2

Answer: The name holds a comma, so COPY quotes the field, and a reader of the file sees one field, not two

RFC 4180 asks for double quotes around a field that holds a comma, a quote or a line break. Without them, the comma in the name would split it into two fields, and every column after it would move.

Loading safely and fast, with measured numbers Pro: opens with a Pro, Premium or Ultimate pass

A bulk load fails in two ways: it stores rows that are wrong, or it takes hours. This part reproduces what each loader does with bad rows, measures what a commit per row costs, and gives the order of steps that keeps a large load fast.

In this part:

What each loader does with a bad row, reproduced
A header row and a moved column, caught by a reject list
One commit per row against one transaction, measured
Indexes, foreign keys and statistics around a large load

5 runnable examples (Python, SQL, copy_errors.py, load_data.py, mysql_server.py, pg_server.py) · about 7 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Exports, file access and formula injection Pro: opens with a Pro, Premium or Ultimate pass

Files are where a database meets the rest of a computer, and an export is where its data meets a spreadsheet. This part covers who may touch which file in each engine, a formula hidden in exported cells with the test that catches it, and how exports write NULLs, headers and quotes.

In this part:

Who may read and write which file
Spreadsheet formulas in an export, reproduced
NULLs, headers and quotes in exports

3 runnable examples (SQL, file_access.py, mysql_server.py, outfile.py, pg_server.py) · about 6 minutes

Opening this part with your pass…Pro part. It opens with the same pass as the part above.

Practise: load the clean orders

The exercise copies the clean rows of an imported orders file into a typed table with one INSERT … SELECT; its sample tests run in your browser:

Exercise · Medium · SQL

Load the clean rows of a CSV import into a typed table

A CSV file of orders was imported with the sqlite3 shell into the staging table raw_orders, so every value is text:

order_ref: the order's reference, unique in the file;
customer: the customer's name, or an empty string when the file left it out;
quantity: the number of items, written by hand in a spreadsheet;
coupon: a coupon code, or an empty string for "no coupon".

The real table is orders (order_ref TEXT PRIMARY KEY, customer TEXT NOT NULL, quantity INTEGER NOT NULL, coupon TEXT), a STRICT table. Write one INSERT … SELECT that copies the clean rows into orders:

skip a row whose customer is empty, or whose quantity is empty or is not a whole number written with digits only ('2' is fine; 'two', '1.5' and '-1' are not);
store quantity as an integer;
store an empty coupon as NULL.

Keep the final SELECT of the starter code: the tests compare the rows it returns, in order.

Starter code · load_orders.sql

-- Copy the clean rows of raw_orders into orders, with real types.
-- So far this copies every row as it is: the STRICT table turns '2' into 2,
-- but refuses a quantity such as 'two'.
INSERT INTO orders (order_ref, customer, quantity, coupon)
SELECT order_ref, customer, quantity, coupon
FROM raw_orders;

SELECT order_ref, customer, quantity, coupon FROM orders ORDER BY order_ref;

The sample tests · tests.yaml

# Sample tests: each one fills raw_orders, runs your script and compares the rows of its final SELECT, in order.
tests:
  - name: copies clean rows with integer quantities
    ordered: true
    setup: |
      CREATE TABLE raw_orders (order_ref TEXT, customer TEXT, quantity TEXT, coupon TEXT);
      CREATE TABLE orders (order_ref TEXT PRIMARY KEY, customer TEXT NOT NULL, quantity INTEGER NOT NULL, coupon TEXT) STRICT;
      INSERT INTO raw_orders VALUES ('A-101', 'Asha Kulkarni', '2', 'DIWALI10'), ('A-102', 'Vikram Nair', '15', 'FIRST50');
    expected:
      columns: [order_ref, customer, quantity, coupon]
      rows:
        - ['A-101', 'Asha Kulkarni', 2, 'DIWALI10']
        - ['A-102', 'Vikram Nair', 15, 'FIRST50']
  - name: skips rows without a customer or with a quantity that is not whole digits
    ordered: true
    setup: |
      CREATE TABLE raw_orders (order_ref TEXT, customer TEXT, quantity TEXT, coupon TEXT);
      CREATE TABLE orders (order_ref TEXT PRIMARY KEY, customer TEXT NOT NULL, quantity INTEGER NOT NULL, coupon TEXT) STRICT;
      INSERT INTO raw_orders VALUES
        ('B-201', 'Meera Rathore', 'two', 'X'),
        ('B-202', '', '3', 'X'),
        ('B-203', 'Tenzin Dorjee', '1.5', 'X'),
        ('B-204', 'Farhan Qureshi', '-1', 'X'),
        ('B-205', 'Lalremruati', '', 'X'),
        ('B-206', 'Gurpreet Kaur', '4', 'X');
    expected:
      columns: [order_ref, customer, quantity, coupon]
      rows:
        - ['B-206', 'Gurpreet Kaur', 4, 'X']
  - name: stores an empty coupon as NULL
    ordered: true
    setup: |
      CREATE TABLE raw_orders (order_ref TEXT, customer TEXT, quantity TEXT, coupon TEXT);
      CREATE TABLE orders (order_ref TEXT PRIMARY KEY, customer TEXT NOT NULL, quantity INTEGER NOT NULL, coupon TEXT) STRICT;
      INSERT INTO raw_orders VALUES ('C-301', 'Kavya Iyer', '1', ''), ('C-302', 'Rohan Das', '7', 'MONSOON');
    expected:
      columns: [order_ref, customer, quantity, coupon]
      rows:
        - ['C-301', 'Kavya Iyer', 1, null]
        - ['C-302', 'Rohan Das', 7, 'MONSOON']

A hint

Three changes to the starter: CAST(quantity AS INTEGER) in the SELECT list, nullif(coupon, '') for the coupon, and a WHERE that keeps only rows with customer <> '', quantity <> '' and quantity NOT GLOB '*[^0-9]*' (a quantity with any character that is not a digit).

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Then practise, weakest idea first:

Interview questions

Fresher to mid · Warm-up

What is the difference between COPY and \copy in PostgreSQL?

Both load rows from a file into a table, or write a query's result to a file, with the same options. COPY is an SQL command that the server runs, so a file name in it is a path on the database server, and only superusers and members of pg_read_server_files or pg_write_server_files may use one. \copy is a command of the psql client: it reads or writes the file on your own computer and streams the data through the connection, so any role that may insert into the table can use it. Use \copy for a file on your laptop or a build machine, and COPY with a file name for a large file that already sits on the server. Both are much faster than one INSERT per row.
Mid

A nightly MySQL job loads a 2-million-row supplier CSV with LOAD DATA LOCAL INFILE. A week later, finance finds 3,100 products priced at 0. No load ever failed. What happened, and how do you make the load fail loudly?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Senior to staff

You must load 50 million rows from CSV files into a PostgreSQL table that serves live traffic and has three indexes and a foreign key. Plan the load, and estimate how long it will take.

Model answer, Pro. First step and follow-ups
Mid to senior

Your service exports customer records to CSV for the support team, who open the files in a spreadsheet. A security review flags the export. What risks does it find, and what do you change?

Model answer, Pro. First step and follow-ups

Key takeaways

Client commands (\copy, LOAD DATA LOCAL, .import) read files on your computer, while server commands (COPY, LOAD DATA INFILE) read files on the server and need file privileges.
CSV has no types and no NULL, so each engine reads an empty field its own way.
Import into a staging table of text columns, check the rows there, then copy the good ones with CAST in one transaction.
Export with COPY … TO or \copy in PostgreSQL, INTO OUTFILE in MySQL, and .mode csv in the sqlite3 shell.

Terms in this lesson

bulk load: Loading the rows of a file into a table with one command, such as PostgreSQL's COPY, MySQL's LOAD DATA or the sqlite3 shell's .import, instead of one INSERT per row.
secure_file_priv: MySQL's setting that limits LOAD DATA INFILE and SELECT … INTO OUTFILE to the files of one folder on the database server.
staging table: A table of plain text columns that a file is loaded into first, so that a query can check every row and report the bad ones with their line before the good ones are copied into the real, typed table.

References

PostgreSQL documentation: COPY (The PostgreSQL Global Development Group)
PostgreSQL documentation: psql (\copy) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Populating a Database (The PostgreSQL Global Development Group)
PostgreSQL documentation: Predefined Roles (The PostgreSQL Global Development Group)
PostgreSQL 17 release notes (COPY ON_ERROR) (The PostgreSQL Global Development Group)
PostgreSQL 18 release notes (COPY REJECT_LIMIT) (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: LOAD DATA Statement (Oracle Corporation)
MySQL 8.4 Reference Manual: Security Considerations for LOAD DATA LOCAL (Oracle Corporation)
MySQL 8.4 Reference Manual: SELECT … INTO Statement (Oracle Corporation)
MySQL 8.4 Reference Manual: Bulk Data Loading for InnoDB Tables (Oracle Corporation)
SQLite: Command Line Shell For SQLite (.import, .mode csv, .once) (SQLite)
RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files (RFC Editor (IETF))
CSV Injection (OWASP Foundation)

Report a problem with this lesson

In this module: Changing data: INSERT, UPDATE, DELETE and UPSERT

INSERT, defaults and getting generated keys Free lesson, Pro answers: 20 minutes
UPDATE and DELETE: changing rows safely Free core, Pro depth: 7 of 20 minutes free
UPSERT and MERGE: insert or update in one step Free core, Pro depth: 8 of 20 minutes free
Bulk loading and exporting CSV Free core, Pro depth: 7 of 20 minutes free

Your country

Bulk loading and exporting CSV

What you will learn

Before you start

What you will learn

Before you start

The command decides which computer reads the file

CSV carries text without types or NULL

A staging table catches bad rows before the real table

An export writes a query’s rows, on the client or the server

Loading safely and fast, with measured numbers Pro: opens with a Pro, Premium or Ultimate pass

Exports, file access and formula injection Pro: opens with a Pro, Premium or Ultimate pass

Practise: load the clean orders

Load the clean rows of a CSV import into a typed table

Results of the sample tests

Interview questions

What is the difference between COPY and \copy in PostgreSQL?

A nightly MySQL job loads a 2-million-row supplier CSV with LOAD DATA LOCAL INFILE. A week later, finance finds 3,100 products priced at 0. No load ever failed. What happened, and how do you make the load fail loudly?

You must load 50 million rows from CSV files into a PostgreSQL table that serves live traffic and has three indexes and a foreign key. Plan the load, and estimate how long it will take.

Your service exports customer records to CSV for the support team, who open the files in a spreadsheet. A security review flags the export. What risks does it find, and what do you change?

Key takeaways

References

Related tools