# CSV to Postgres Import: Don't Lose the Types
The Comma-Separated Values (CSV) file is the undisputed lingua franca of data exchange. Whether you are migrating from a legacy system, ingesting a third-party vendor's daily export, or moving data from an analytical notebook into a production database, CSVs are everywhere. However, this ubiquity hides a dangerous trap: CSVs are inherently "dumb." They store everything as unstructured text. When importing a CSV into PostgreSQL, the most common mistake developers make is allowing their meticulously typed data—integers, timestamps, booleans, and decimals—to degrade into generic `TEXT` or `VARCHAR` columns.
Losing your data types during an import doesn't just bloat your database; it breaks indexing, destroys query performance, and invites silent data corruption. Fortunately, with a little schema discipline and PostgreSQL’s native tooling, you can ensure your data arrives with its types fully intact.
The Typeless Trap of CSVs
A CSV file is essentially a flat text document. If you open a CSV in a text editor, you will see that the number `123`, the boolean `true`, and the date `2023-10-25` are all just strings of characters. There is no metadata telling the database, "This is an integer," or "This is a timestamp."
If you rely on automated tools that guess your schema, or if you lazily create a table where every column is `TEXT`, PostgreSQL will accept almost anything you throw at it. But the moment you try to perform arithmetic on a string, or filter by a date range, you will be forced to cast types on the fly. This leads to full table scans, ignored indexes, and a database that crawls under the weight of its own unoptimized data.
The Foundation: Explicit Schema Definition
The golden rule of importing CSVs into PostgreSQL is to never let the database guess your types. Before you run a single import command, you must explicitly define your table schema.
Instead of creating a table like this:
```sql
CREATE TABLE bad_sales_data (
order_id TEXT,
amount TEXT,
is_shipped TEXT,
order_date TEXT
);
```
You must enforce strict typing at the point of table creation:
```sql
CREATE TABLE good_sales_data (
order_id SERIAL PRIMARY KEY,
amount NUMERIC(10,2) NOT NULL,
is_shipped BOOLEAN DEFAULT FALSE,
order_date TIMESTAMP WITH TIME ZONE
);
```
By defining your types upfront, you create a strict gatekeeper. PostgreSQL will actively reject malformed data—like trying to insert the string "N/A" into the `NUMERIC` amount column—saving you from silent data corruption down the line.
The Standard Approach: Using the `COPY` Command
Once your typed table exists, PostgreSQL provides a highly optimized, native tool for bulk data insertion: the `COPY` command (or the `\copy` wrapper if you are executing via `psql` without superuser permissions).
The `COPY` command is remarkably fast, but its true power lies in how it handles type