Skip to content

Instantly share code, notes, and snippets.

@Sophrinix

Sophrinix/gist:daebed85198d30da56cc9eab95f52da5

Created August 16, 2018 04:01
Show Gist options
  • Save Sophrinix/daebed85198d30da56cc9eab95f52da5 to your computer and use it in GitHub Desktop.
Save Sophrinix/daebed85198d30da56cc9eab95f52da5 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL COPY FROM PATCH 1745
Raw
gistfile1.txt
# test unique violation
# test exclusion constraint violation
# output from unique violation (via ON CONFLICT IGNORE argument)
# output exclusion constraint violation (via ON CONFLICT IGNORE argument) (using \! {shell_command possibly using cat})
# GUC variable not found
test=# CREATE TABLE onek (
test(# unique1 int4,
test(# unique2 int4,
test(# two int4,
test(# four int4,
test(# ten int4,
test(# twenty int4,
test(# hundred int4,
test(# thousand int4,
test(# twothousand int4,
test(# fivethous int4,
test(# tenthous int4,
test(# odd int4,
test(# even int4,
test(# stringu1 name,
test(# stringu2 name,
test(# string4 name
test(# );
CREATE TABLE
test=# COPY onek FROM '@abs_srcdir@/data/onek.data';
COPY 1000
test=# select * from onek;
test=# exitr
test-# \h COPY
test-# COPY onek FROM '@abs_srcdir@/data/onek.data' ON CONFLICT IGNORE;
ERROR: syntax error at or near "exitr"
LINE 1: exitr
^
test=# COPY onek FROM '@abs_srcdir@/data/onek.data' ON CONFLICT IGNORE;
ERROR: Failed record file name must be specified
test=# COPY onek FROM '@abs_srcdir@/data/onek.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 1000
test=# COPY onek FROM '@abs_srcdir@/data/onek.malformed.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
ERROR: invalid input syntax for type integer: "| 7"
CONTEXT: COPY onek, line 1, column hundred: "| 7"
test=# s
test=#
test=#
test=# select * from onek
test-# ;
test=# truncate onek;
TRUNCATE TABLE
test=# select * from onek;
test=# COPY onek FROM '@abs_srcdir@/data/onek.malformed.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
ERROR: invalid input syntax for type integer: "| 7"
CONTEXT: COPY onek, line 1, column hundred: "| 7"
test=# select * from onek;
test=# CREATE TABLE onek ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name );
ERROR: relation "onek" already exists
test=# CREATE TABLE onek ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name );
ERROR: relation "onek" already exists
test=# CREATE TABLE onek_constrained (
test(# unique1
test(# unique1int4 PRIMARY KEY,
test(# unique2
test(# unique2int4,
test(# two
test(# two
test(# twoint4,
test(# four
test(# fourint4,
test(# ten
test(# ten
test(# tenint4,
test(# twenty
test(# twentyint4,
test(# hundred
test(# hundredint4,
test(# thousandint4,
test(# twothousandint4,
test(# fivethousint4,
test(# tenthousint4,
test(# odd
test(# odd
test(# oddint4,
test(# even
test(# evenint4,
test(# stringu1name,
test(# stringu2name,
test(# string4
test(# string4name
test(# );
ERROR: syntax error at or near "PRIMARY"
LINE 2: unique1int4 PRIMARY KEY,
^
test=# CREATE TABLE onek_constrained ( unique1 int4 PRIMARY KEY, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name );
CREATE TABLE
test=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+---------+---------+-------------
public | onek | table | andrewm | 0 bytes |
public | onek_constrained | table | andrewm | 0 bytes |
(2 rows)
test=# \d+ onek
onek onek_constrained onek_constrained_pkey
test=# \d+ onek
onek onek_constrained onek_constrained_pkey
test=# \d+ onek_constrained
Table "public.onek_constrained"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+---------+---------+--------------+-------------
unique1 | integer | | not null | | plain | |
unique2 | integer | | | | plain | |
two | integer | | | | plain | |
four | integer | | | | plain | |
ten | integer | | | | plain | |
twenty | integer | | | | plain | |
hundred | integer | | | | plain | |
thousand | integer | | | | plain | |
twothousand | integer | | | | plain | |
fivethous | integer | | | | plain | |
tenthous | integer | | | | plain | |
odd | integer | | | | plain | |
even | integer | | | | plain | |
stringu1 | name | | | | plain | |
stringu2 | name | | | | plain | |
string4 | name | | | | plain | |
Indexes:
"onek_constrained_pkey" PRIMARY KEY, btree (unique1)
test=# COPY onek FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 10
test=# select * onek_
test=# select * onek_
test=# select count(*) onek
test=# select count(*) onek
test=# select count(*) onek_constrained
test-# ;
onek_constrained
------------------
1
(1 row)
test=# select * from onek_constrained;
test=# COPY onek_constrained FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 5
test=# select * from onek_constrained; test=# COPY onek_constrained FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 0
test=# COPY onek_constrained FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 0
test=# show copy_
test=# show copy_
test=# show copy_max_error_limit
test-# ;
ERROR: unrecognized configuration parameter "copy_max_error_limit"
test=# COPY onek_constrained FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 0
test=# COPY onek_constrained FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 0
test=# show copy_max_error_limit ;
ERROR: unrecognized configuration parameter "copy_max_error_limit"
test=#
test=# describe onek_constrained;
ERROR: syntax error at or near "describe"
LINE 1: describe onek_constrained;
^
test=# describe onek_constrained;
ERROR: syntax error at or near "describe"
LINE 1: describe onek_constrained;
^
test=# \d+ onek_constrained;
Table "public.onek_constrained"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+---------+---------+--------------+-------------
unique1 | integer | | not null | | plain | |
unique2 | integer | | | | plain | |
two | integer | | | | plain | |
four | integer | | | | plain | |
ten | integer | | | | plain | |
twenty | integer | | | | plain | |
hundred | integer | | | | plain | |
thousand | integer | | | | plain | |
twothousand | integer | | | | plain | |
fivethous | integer | | | | plain | |
tenthous | integer | | | | plain | |
odd | integer | | | | plain | |
even | integer | | | | plain | |
stringu1 | name | | | | plain | |
stringu2 | name | | | | plain | |
string4 | name | | | | plain | |
Indexes:
"onek_constrained_pkey" PRIMARY KEY, btree (unique1)
test=# CREATE TABLE onek_constrained_excluded (
test(# unique1 int4 PRIMARY KEY,
test(# unique2 int4,
test(# two int4,
test(# four int4,
test(# ten int4,
test(# twenty int4,
test(# hundred int4,
test(# thousand int4,
test(# twothousand int4,
test(# fivethous int4,
test(# tenthous int4,
test(# odd int4,
test(# even int4,
test(# stringu1 name,
test(# stringu2 name,
test(# string4 name,
test(# EXCLUDE USING btree (unique2 WITH =)
test(# );
CREATE TABLE
test=# COPY onek_constrained_excluded FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 5
test=# CREATE TABLE onek_excluded (
test(# unique1 int4,
test(# unique2 int4,
test(# two int4,
test(# four int4,
test(# ten int4,
test(# twenty int4,
test(# hundred int4,
test(# thousand int4,
test(# twothousand int4,
test(# fivethous int4,
test(# tenthous int4,
test(# odd int4,
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx
931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx
714 | 2 | 0 | 2 | 4 | 14 | 4 | 14 | 114 | 214 | 714 | 8 | 9 | MBAAAA | CAAAAA | OOOOxx
711 | 3 | 1 | 3 | 1 | 11 | 1 | 11 | 111 | 211 | 711 | 2 | 3 | JBAAAA | DAAAAA | VVVVxx
883 | 4 | 1 | 3 | 3 | 3 | 3 | 83 | 83 | 383 | 883 | 6 | 7 | ZHAAAA | EAAAAA | AAAAxx
(5 rows)
...skipping...
test(# even int4,
test(# stringu1 name,
test(# stringu2 name,
test(# string4 name,
test(# EXCLUDE USING btree (unique2 WITH =)
test(# );
CREATE TABLE
test=# COPY onek_excluded FROM '@abs_srcdir@/data/onek.dup.data' ON CONFLICT IGNORE '@HOME_DIR@/broken.csv';
COPY 5
test=# select * onek_excluded;
ERROR: syntax error at or near "onek_excluded"
LINE 1: select * onek_excluded;
^
test=# select * FROM onek_excluded;
test=# select unique2 FROM onek_excluded;
unique2
---------
0
1
2
3
4
(5 rows)
test=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment