Efficient data import and export strategies

8072 views

                  -- Import CSV with COPY (fastest method)
COPY users (username, email, age, created_at)
FROM '/path/to/users.csv'
WITH (
  FORMAT csv,
  HEADER true,
  DELIMITER ',',
  QUOTE '"',
  NULL 'NULL'
);

-- Export to CSV
COPY users TO '/path/to/users_export.csv'
WITH (
  FORMAT csv,
  HEADER true,
  DELIMITER ',',
  QUOTE '"',
  NULL 'NULL'
);

-- Export query results
COPY (
  SELECT username, email, created_at
  FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
) TO '/path/to/active_users.csv'
WITH (FORMAT csv, HEADER true);

-- Import tab-delimited file
COPY products (sku, name, price)
FROM '/path/to/products.tsv'
WITH (FORMAT text, DELIMITER E'\t');

-- Import with different NULL representation
COPY sales (date, product_id, quantity, revenue)
FROM '/path/to/sales.csv'
WITH (FORMAT csv, NULL '\N');

-- Binary format (faster, PostgreSQL-specific)
COPY large_table TO '/path/to/data.bin'
WITH (FORMAT binary);

COPY large_table FROM '/path/to/data.bin'
WITH (FORMAT binary);

-- Stream from stdin (pipe from application)
COPY users (username, email) FROM STDIN WITH CSV;
-- Application sends data
-- \. to end

-- Stream to stdout
COPY users TO STDOUT WITH CSV HEADER;

-- psql \copy command (client-side, for non-superuser)
-- \copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true);

-- Import large file efficiently
BEGIN;

-- Disable triggers during import
ALTER TABLE users DISABLE TRIGGER ALL;

-- Drop indexes temporarily
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_users_username;

-- Import data
COPY users FROM '/path/to/large_users.csv' WITH (FORMAT csv, HEADER true);

-- Recreate indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- Re-enable triggers
ALTER TABLE users ENABLE TRIGGER ALL;

COMMIT;

-- Analyze after large import
ANALYZE users;

-- Import with data transformation
COPY (
  SELECT
    id,
    UPPER(username) AS username,
    LOWER(email) AS email,
    CURRENT_TIMESTAMP AS imported_at
  FROM staging_users
) TO '/path/to/transformed.csv' WITH CSV;

-- Handle import errors (PostgreSQL 14+)
COPY users FROM '/path/to/users.csv'
WITH (
  FORMAT csv,
  HEADER true,
  ON_ERROR ignore  -- Skip error rows
);

-- Export JSON
COPY (
  SELECT jsonb_build_object(
    'id', id,
    'username', username,
    'email', email,
    'created_at', created_at
  )
  FROM users
) TO '/path/to/users.json';

-- Export JSONL (JSON Lines)
COPY (
  SELECT row_to_json(users.*)
  FROM users
) TO '/path/to/users.jsonl';

-- Incremental export (only new/updated records)
COPY (
  SELECT *
  FROM users
  WHERE updated_at > (
    SELECT last_export_time
    FROM export_log
    WHERE table_name = 'users'
  )
) TO '/path/to/users_incremental.csv' WITH CSV HEADER;

-- Update last export time
UPDATE export_log
SET last_export_time = CURRENT_TIMESTAMP
WHERE table_name = 'users';

                  -- Staging table pattern
CREATE TEMP TABLE staging_users (
  username VARCHAR(50),
  email VARCHAR(100),
  age INT,
  status VARCHAR(20)
);

-- Import to staging
COPY staging_users FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER true);

-- Validate and transform
INSERT INTO users (username, email, age, status)
SELECT
  TRIM(username),
  LOWER(TRIM(email)),
  age,
  COALESCE(status, 'active')
FROM staging_users
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'
  AND age > 0
  AND age < 150;

-- Check rejected records
SELECT *
FROM staging_users
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'
   OR age <= 0
   OR age >= 150;

-- Upsert pattern (insert or update)
INSERT INTO products (sku, name, price, updated_at)
SELECT sku, name, price::DECIMAL, CURRENT_TIMESTAMP
FROM staging_products
ON CONFLICT (sku)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = CURRENT_TIMESTAMP;

-- Parallel import with pg_bulkload (extension)
-- pg_bulkload -i users.csv -O users -o "TYPE=CSV"

-- Foreign data wrapper for direct import
CREATE EXTENSION file_fdw;

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csv_users (
  username TEXT,
  email TEXT,
  age TEXT
)
SERVER file_server
OPTIONS (filename '/path/to/users.csv', format 'csv', header 'true');

-- Query CSV directly
SELECT * FROM csv_users LIMIT 10;

-- Import from foreign table
INSERT INTO users (username, email, age)
SELECT username, email, age::INT
FROM csv_users;

-- MySQL import/export
/*
-- MySQL LOAD DATA
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(username, email, age);

-- MySQL SELECT INTO OUTFILE
SELECT * FROM users
INTO OUTFILE '/path/to/users_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- mysqldump for export
mysqldump -u root -p mydb users > users_backup.sql

-- mysql for import
mysql -u root -p mydb < users_backup.sql
*/

-- Batch insert from application
/*
-- Node.js example
const batchSize = 1000;
const batches = _.chunk(rows, batchSize);

for (const batch of batches) {
  const values = batch.map(row =>
    `(${pg.escape(row.username)}, ${pg.escape(row.email)})`
  ).join(',');

  await client.query(
    `INSERT INTO users (username, email) VALUES ${values}`
  );
}

// Better: Use COPY from stream
const stream = client.query(
  copyFrom('COPY users (username, email) FROM STDIN WITH (FORMAT csv)')
);

for (const row of rows) {
  stream.write(`${row.username},${row.email}\n`);
}

stream.end();
*/

-- Partition-aware import
CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Import directly to partition
COPY orders_2024_01 FROM '/path/to/january_orders.csv'
WITH (FORMAT csv, HEADER true);

-- Cross-database import (postgres_fdw)
CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host', dbname 'sourcedb', port '5432');

CREATE USER MAPPING FOR current_user
  SERVER remote_db
  OPTIONS (user 'remote_user', password 'password');

IMPORT FOREIGN SCHEMA public
  LIMIT TO (users, orders)
  FROM SERVER remote_db
  INTO public;

-- Copy data from remote database
INSERT INTO local_users
SELECT * FROM users;  -- users is foreign table

-- Compression during export
-- COPY users TO PROGRAM 'gzip > /path/to/users.csv.gz' WITH CSV;

-- Decompression during import
-- COPY users FROM PROGRAM 'gunzip < /path/to/users.csv.gz' WITH CSV;

Data import/export moves data between systems. I use COPY for bulk operations—orders of magnitude faster than INSERT. CSV format balances simplicity and performance. Binary format is faster but less portable. Streaming import handles large files without memory issues. Parallel import utilizes multiple cores. ETL pipelines transform data during import. Understanding delimiters, quoting, escaping prevents data corruption. Header rows simplify column mapping. NULL handling requires explicit configuration. Foreign key constraints can slow imports—disable temporarily. Batching balances transaction size and performance. pg_dump exports databases reliably. JSON export integrates with modern APIs. Proper import/export strategy is essential for data migration, backups, analytics pipelines.

Maria Garcia

More from Maria Garcia

Efficient data import and export strategies

0 Comments

More from Maria Garcia