Efficient data import and export strategies

-- 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;