test.sh.txt

text/plain

Download

#!/bin/bash

set -e
set -o pipefail
set -u

PG="psql -X -U postgres -h localhost -d postgres"

echo " ======= Create table t"

${PG} <<EOF
DROP TABLE IF EXISTS t;
CREATE UNLOGGED TABLE t (id INT PRIMARY KEY, filler TEXT);
EOF


echo " ======= Text, no special characters; create /tmp/t_none.txt"

${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A', 4096)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_none.txt' (FORMAT text);
EOF

echo " ======= Text, no special characters; load times"

${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
EOF

rm /tmp/t_none.txt


echo " ======= CSV, no special characters; create /tmp/t_none.csv"

${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A', 4096)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_none.csv' (FORMAT csv, QUOTE '"');
EOF

echo " ======= CSV, no special characters; load times"

${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
EOF

rm /tmp/t_none.csv


echo " ======= Text, with 1/3 escapes; create /tmp/t_escape.txt"

${PG} <<'EOF'
truncate t;
INSERT INTO t
SELECT s, repeat('A\A', 1365)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_escape.txt' (FORMAT text);
EOF

echo " ======= Text, with 1/3 escapes; load times"

${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
EOF

rm /tmp/t_escape.txt


echo " ======= CSV, with 1/3 quotes; create /tmp/t_quote.csv"

${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A"A', 1365)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_quote.csv' (FORMAT csv, QUOTE '"');
EOF

echo " ======= CSV, with 1/3 quotes; load times"

${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
EOF

rm /tmp/t_quote.csv

echo " ======= Drop table t"

${PG} <<EOF
DROP TABLE IF EXISTS t;
EOF