Data Import, Export, and Integrity Constraints

Data Import, Export, and Integrity Constraints

Why This Lesson Matters

Real-world data rarely arrives clean or in the exact format a database expects. As a data analyst, you will spend a significant portion of your time loading data from external sources, fixing the problems that surface during loading, and setting up guardrails so that bad data cannot enter the database in the first place. PostgreSQL provides powerful tools for all three tasks: the `COPY` command for bulk loading and exporting, and a set of constraint types that enforce data quality rules at the database level.

---

Bulk Loading with COPY and \copy

PostgreSQL offers two mechanisms for bulk data transfer.

Server-side COPY runs with the permissions of the PostgreSQL server process. It reads from (or writes to) a file path that the server can see on its own filesystem. Because the server process owns the operation, you must have superuser or the `pg_read_server_files` role to use it.

```sql

COPY orders (order_id, customer_id, order_date, total)

FROM '/data/orders.csv'

WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',', ENCODING 'UTF8');

```

Client-side \copy (note the backslash) is a `psql` meta-command. It streams the file from the client machine through the connection, so no special server privileges are needed. The syntax is nearly identical:

```sql

\copy orders (order_id, customer_id, order_date, total)

FROM 'orders.csv'

WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',');

```

Use `\copy` in classroom and most professional environments where you do not control the server filesystem.

Common import problems and fixes

| Problem | Symptom | Fix |

|---|---|---|

| Wrong delimiter | "extra columns" error | Set `DELIMITER '|'` or `DELIMITER E'\t'` for tabs |

| Date format mismatch | invalid input syntax for type date | Run `SET datestyle = 'ISO, MDY';` before the COPY, or cast in a staging table |

| Non-UTF-8 encoding | invalid byte sequence | Add `ENCODING 'LATIN1'` or recode the file with `iconv` |

| Header row present | first row inserted as data | Add `HEADER TRUE` to the options |

Always document each error message exactly as PostgreSQL returns it and the SQL statement you used to fix it. That log is part of your deliverable today.

---

Integrity Constraints

Constraints are rules attached to a table that PostgreSQL enforces automatically on every `INSERT`, `UPDATE`, and `COPY`. They are your first line of defense against dirty data.

NOT NULL — guarantees a column always has a value. Use it for any column that is required by business rules.

```sql

ALTER TABLE customers ADD CONSTRAINT customers_email_nn NOT NULL (email);

-- Or at table creation:

created_at TIMESTAMP NOT NULL DEFAULT NOW()

```

UNIQUE — guarantees no two rows share the same value in the specified column or column combination.

```sql

ALTER TABLE customers ADD CONSTRAINT customers_email_unique UNIQUE (email);

```

CHECK — evaluates a Boolean expression; the row is rejected if the expression is false. Use it for domain validation such as price ranges or status codes.

```sql

ALTER TABLE products ADD CONSTRAINT products_price_positive CHECK (price > 0);

```

PRIMARY KEY — combines NOT NULL and UNIQUE; every table should have one.

FOREIGN KEY — links a column in one table to the primary key of another, preventing orphaned records.

```sql

ALTER TABLE orders

ADD CONSTRAINT orders_customer_fk

FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

ON DELETE RESTRICT;

```

When you attempt to insert a row that violates any constraint, PostgreSQL returns an error with the constraint name in the message. Record this name and explain the data quality problem the constraint prevents.

---

Exporting Data

Export to CSV — mirror of the import syntax:

```sql

COPY (

SELECT order_id, order_date, total

FROM orders

WHERE total > 100

) TO '/tmp/large_orders.csv'

WITH (FORMAT CSV, HEADER TRUE);

```

Or with `\copy` from psql:

```sql

\copy (SELECT order_id, order_date, total FROM orders WHERE total > 100)

TO 'large_orders.csv' WITH (FORMAT CSV, HEADER TRUE);

```

Export to JSON — PostgreSQL's `json_agg` aggregates rows into a JSON array:

```sql

COPY (

SELECT json_agg(t)

FROM (

SELECT order_id, order_date, total

FROM orders

WHERE total > 100

) t

) TO '/tmp/large_orders.json';

```

Verify the output in Python:

```python

import pandas as pd, json

with open('large_orders.json') as f:

data = json.load(f)

df = pd.DataFrame(data)

print(df.head())

```

---

Safety and Professional Practices

• Never import directly into a production table. Load into a staging table first, validate, then insert into the target.
• Back up before altering constraints on a populated table. Adding a constraint on existing data triggers a full-table scan; if rows already violate the rule, the command will fail and could leave a partially-altered schema.
• Protect file paths. Files written by the server-side COPY command inherit the server process's OS permissions. Store exports in directories with restricted access, not world-readable temp folders in sensitive environments.
• Encoding matters for compliance. If you work with data containing personal information, verify that encoding conversions do not silently corrupt characters in names or addresses.