How-to

Import car makes and models into MySQL or PostgreSQL

The CarsDataset download is a UTF-8 CSV with one row per variant (47,344 rows, 39 columns). These steps load it into PostgreSQL, MySQL or SQLite in a few minutes. You can follow along with the free sample.

1. Create the table

The column names match the CSV header exactly. Types are what we use in our own PostgreSQL database.

CREATE TABLE cars (
  id                         INTEGER PRIMARY KEY,
  brand                      VARCHAR(200),
  model                      VARCHAR(200),
  year                       INTEGER,
  variant_name               TEXT,
  trim                       VARCHAR(500),
  segment                    VARCHAR(100),
  body_type                  VARCHAR(200),
  doors                      INTEGER,
  seats                      INTEGER,
  fuel_type                  VARCHAR(50),
  engine_type                VARCHAR(100),
  cubic_capacity_cc          INTEGER,
  cylinders                  INTEGER,
  aspiration                 VARCHAR(100),
  cylinder_config            VARCHAR(100),
  power_kw                   INTEGER,
  power_hp                   INTEGER,
  torque_nm                  INTEGER,
  transmission_type          VARCHAR(50),
  gears                      INTEGER,
  drive_type                 VARCHAR(50),
  length_mm                  INTEGER,
  width_mm                   INTEGER,
  height_mm                  INTEGER,
  wheelbase_mm               INTEGER,
  curb_weight_kg             INTEGER,
  top_speed_kmh              INTEGER,
  acceleration_0_100_s       NUMERIC(4,1),
  fuel_consumption_combined  NUMERIC(5,1),
  fuel_consumption_city      NUMERIC(5,1),
  fuel_consumption_highway   NUMERIC(5,1),
  co2_emissions_gkm          INTEGER,
  price_eur                  NUMERIC(12,2),
  is_ev                      BOOLEAN,
  is_hybrid                  BOOLEAN,
  is_phev                    BOOLEAN,
  is_supercar                BOOLEAN,
  features                   TEXT   -- JSON array; use JSONB in PostgreSQL, JSON in MySQL
);

In PostgreSQL, change features TEXT to features JSONB; in MySQL, to features JSON. Booleans in the CSV are true/false, empty fields are missing values.

2a. PostgreSQL: COPY

-- from psql (client-side file)
\copy cars FROM 'data/cars.csv' WITH (FORMAT csv, HEADER true, NULL '', ENCODING 'UTF8');

CREATE INDEX cars_brand_model_year ON cars (brand, model, year);

The file starts with a UTF-8 byte-order mark so Excel opens it cleanly. Recent PostgreSQL versions skip it; if your first column name arrives as \ufeffid, strip the BOM first with sed -i '1s/^\xEF\xBB\xBF//' data/cars.csv.

2b. MySQL / MariaDB: LOAD DATA

SET GLOBAL local_infile = 1;  -- once, if LOCAL is disabled

LOAD DATA LOCAL INFILE 'data/cars.csv'
INTO TABLE cars
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

CREATE INDEX cars_brand_model_year ON cars (brand, model, year);

Use utf8mb4 so brand and model names with accents (Škoda, Citroën) survive. MySQL loads empty strings into numeric columns as 0 in non-strict mode; to keep them as NULL, load into variables and use NULLIF(@col,'').

2c. SQLite

sqlite3 cars.db
.mode csv
.import --skip 1 data/cars.csv cars

3. Useful queries

-- makes for a dropdown
SELECT brand, COUNT(*) AS variants FROM cars GROUP BY brand ORDER BY brand;

-- models of one make, with year range
SELECT model, MIN(year) AS first_year, MAX(year) AS last_year, COUNT(*) AS variants
FROM cars WHERE brand = 'BMW' GROUP BY model ORDER BY variants DESC;

-- variants of a model in one year
SELECT variant_name, power_hp, torque_nm, fuel_type, fuel_consumption_combined
FROM cars WHERE brand = 'BMW' AND model = '3 Series' AND year = 2024
ORDER BY power_hp;

-- PostgreSQL: cars that list adaptive cruise control
SELECT brand, model, year FROM cars
WHERE features @> '["Adaptive cruise control"]'::jsonb LIMIT 20;

Exact feature names vary by source; search with ILIKE on features::text to explore them first.

4. Keep it fresh

Cars purchases include two quarterly refreshes in the first six months. Re-import into a staging table and swap, or upsert on id, which is stable between exports. If you would rather not manage files, the REST API serves the same data as JSON.

See what the rows look like for a make: BMW, Audi, Renault, Peugeot.