src/test/regress/sql/copy.sql - master - githubmirror/postgres

src/test/regress/sql/copy.sql
557 строк16 KB
Tom Lane
Teach psql to skip in-line COPY ... FROM STDIN data after a failure.
10 авг 2026, 16:37
10 авг 2026, 16:373045a25
--
-- COPY
--

-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
\getenv abs_builddir PG_ABS_BUILDDIR

--- test copying in CSV mode with various styles
--- of embedded line ending characters

create temp table copytest (
	style	text,
	test 	text,
	filler	int);

insert into copytest values('DOS',E'abc\r\ndef',1);
insert into copytest values('Unix',E'abc\ndef',2);
insert into copytest values('Mac',E'abc\rdef',3);
insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);

\set filename :abs_builddir '/results/copytest.csv'
copy copytest to :'filename' csv;

create temp table copytest2 (like copytest);

copy copytest2 from :'filename' csv;

select * from copytest except select * from copytest2;

truncate copytest2;

--- same test but with an escape char different from quote char

copy copytest to :'filename' csv quote '''' escape E'\\';

copy copytest2 from :'filename' csv quote '''' escape E'\\';

select * from copytest except select * from copytest2;

--- test unquoted \. as data inside CSV
-- do not use copy out to export the data, as it would quote \.
\o :filename
\qecho line1
\qecho '\\.'
\qecho line2
\o
-- get the data back in with copy
truncate copytest2;
copy copytest2(test) from :'filename' csv;
select test from copytest2 order by test collate "C";

-- in text mode, \. must be alone on its line
truncate copytest2;
copy copytest2(test) from stdin;
line1
line2
foo\.
line3
\.
copy copytest2(test) from stdin;
line4
line5
\.foo
line6
\.
select test from copytest2;


-- test header line feature

create temp table copytest3 (
	c1 int,
	"col with , comma" text,
	"col with "" quote"  int);

copy copytest3 from stdin csv header;
this is just a line full of junk that would error out if parsed
1,a,1
2,b,2
\.

copy copytest3 to stdout csv header;

-- testing explicit column order
create temp table copytest_order (a int, b int, c int);
copy copytest_order from stdin;
1	2	3
\.
copy copytest_order (c, b, a) to stdout;
copy copytest_order (c, b, a) to stdout (format csv);
copy copytest_order (c, b, a) to stdout (format json);

--- test copying in JSON mode with various styles
copy (select 1 union all select 2) to stdout with (format json);
copy (select 1 as foo union all select 2) to stdout with (format json);
copy (values (1), (2)) TO stdout with (format json);
copy (select 1 union all select 2) to stdout with (format json, force_array true);
copy (values (1), (2)) TO stdout with (format json, force_array true);
copy copytest to stdout json;
copy copytest to stdout (format json);
copy (select * from copytest) to stdout (format json);

-- all of the following should yield error
copy copytest to stdout (format json, delimiter '|');
copy copytest to stdout (format json, null '\N');
copy copytest to stdout (format json, default '|');
copy copytest to stdout (format json, header);
copy copytest to stdout (format json, header 1);
copy copytest to stdout (format json, quote '"');
copy copytest to stdout (format json, escape '"');
copy copytest to stdout (format json, force_quote *);
copy copytest to stdout (format json, force_not_null *);
copy copytest to stdout (format json, force_null *);
copy copytest to stdout (format json, on_error ignore);
copy copytest to stdout (format json, reject_limit 1);
copy copytest from stdin(format json);
\.
-- all of the above should yield error

-- column list with json format
copy copytest (style, test, filler) to stdout (format json);

-- should fail: force_array requires json format
copy copytest to stdout (format csv, force_array true);

-- force_array variants
copy copytest to stdout (format json, force_array);
copy copytest(style, test) to stdout (format json, force_array true);
copy copytest to stdout (format json, force_array false);

-- force_array with empty result set
copy (select 1 where false) to stdout (format json, force_array);

-- column list with diverse data types
create temp table copyjsontest_types (
    id int,
    js json,
    jsb jsonb,
    arr int[],
    n numeric(10,2),
    b boolean,
    ts timestamp,
    t text);

insert into copyjsontest_types values
(1, '{"a":1}', '{"b":2}', '{1,2,3}', 3.14, true,
 '2024-01-15 10:30:00', 'hello'),
(2, '[1,null,"x"]', '{"nested":{"k":"v"}}', '{4,5}', -99.99, false,
 '2024-06-30 23:59:59', 'world'),
(3, 'null', 'null', '{}', null, null, null, null);

-- full table
copy copyjsontest_types to stdout (format json);

-- column subsets exercising each type
copy copyjsontest_types (id, js, jsb) to stdout (format json);
copy copyjsontest_types (id, arr, n, b) to stdout (format json);
copy copyjsontest_types (jsb, t) to stdout (format json);
copy copyjsontest_types (id, ts) to stdout (format json);

-- single column: json and jsonb
copy copyjsontest_types (js) to stdout (format json);
copy copyjsontest_types (jsb) to stdout (format json);

drop table copyjsontest_types;

-- embedded escaped characters
create temp table copyjsontest (
    id bigserial,
    f1 text,
    f2 timestamptz);

insert into copyjsontest
  select g.i,
         CASE WHEN g.i % 2 = 0 THEN
           'line with '' in it: ' || g.i::text
         ELSE
           'line with " in it: ' || g.i::text
         END,
         'Mon Feb 10 17:32:01 1997 PST'
  from generate_series(1,5) as g(i);

insert into copyjsontest (f1) values
(E'aaa\"bbb'::text),
(E'aaa\\bbb'::text),
(E'aaa\/bbb'::text),
(E'aaa\bbbb'::text),
(E'aaa\fbbb'::text),
(E'aaa\nbbb'::text),
(E'aaa\rbbb'::text),
(E'aaa\tbbb'::text);

copy copyjsontest to stdout json;

create temp table copytest4 (
	c1 int,
	"colname with tab: 	" text);

copy copytest4 from stdin (header);
this is just a line full of junk that would error out if parsed
1	a
2	b
\.

copy copytest4 to stdout (header);

-- test multi-line header line feature

create temp table copytest5 (c1 int);

copy copytest5 from stdin (format csv, header 2);
this is a first header line.
this is a second header line.
1
2
\.
copy copytest5 to stdout (header);

truncate copytest5;
copy copytest5 from stdin (format csv, header 4);
this is a first header line.
this is a second header line.
1
2
\.
select count(*) from copytest5;

truncate copytest5;
copy copytest5 from stdin (format csv, header 5);
this is a first header line.
this is a second header line.
1
2
\.
select count(*) from copytest5;

-- test header line feature (given as strings)
truncate copytest5;
copy copytest5 from stdin (format csv, header '0');
1
2
\.
select * from copytest5 order by c1;

truncate copytest5;
copy copytest5 from stdin (format csv, header '1');
1
2
\.
select * from copytest5 order by c1;

-- test copy from with a partitioned table
create table parted_copytest (
	a int,
	b int,
	c text
) partition by list (b);

create table parted_copytest_a1 (c text, b int, a int);
create table parted_copytest_a2 (a int, c text, b int);

alter table parted_copytest attach partition parted_copytest_a1 for values in(1);
alter table parted_copytest attach partition parted_copytest_a2 for values in(2);

-- We must insert enough rows to trigger multi-inserts.  These are only
-- enabled adaptively when there are few enough partition changes.
insert into parted_copytest select x,1,'One' from generate_series(1,1000) x;
insert into parted_copytest select x,2,'Two' from generate_series(1001,1010) x;
insert into parted_copytest select x,1,'One' from generate_series(1011,1020) x;

\set filename :abs_builddir '/results/parted_copytest.csv'
copy (select * from parted_copytest order by a) to :'filename';

truncate parted_copytest;

copy parted_copytest from :'filename';

-- Ensure COPY FREEZE errors for partitioned tables.
begin;
truncate parted_copytest;
copy parted_copytest from :'filename' (freeze);
rollback;

select tableoid::regclass,count(*),sum(a) from parted_copytest
group by tableoid order by tableoid::regclass::name;

truncate parted_copytest;

-- create before insert row trigger on parted_copytest_a2
create function part_ins_func() returns trigger language plpgsql as $$
begin
  return new;
end;
$$;

create trigger part_ins_trig
	before insert on parted_copytest_a2
	for each row
	execute procedure part_ins_func();

copy parted_copytest from :'filename';

select tableoid::regclass,count(*),sum(a) from parted_copytest
group by tableoid order by tableoid::regclass::name;

truncate table parted_copytest;
create index on parted_copytest (b);
drop trigger part_ins_trig on parted_copytest_a2;

copy parted_copytest from stdin;
1	1	str1
2	2	str2
\.

-- Ensure index entries were properly added during the copy.
select * from parted_copytest where b = 1;
select * from parted_copytest where b = 2;

drop table parted_copytest;

--
-- Progress reporting for COPY
--
create table tab_progress_reporting (
	name text,
	age int4,
	location point,
	salary int4,
	manager name
);

-- Add a trigger to catch and print the contents of the catalog view
-- pg_stat_progress_copy during data insertion.  This allows to test
-- the validation of some progress reports for COPY FROM where the trigger
-- would fire.
create function notice_after_tab_progress_reporting() returns trigger AS
$$
declare report record;
begin
  -- The fields ignored here are the ones that may not remain
  -- consistent across multiple runs.  The sizes reported may differ
  -- across platforms, so just check if these are strictly positive.
  with progress_data as (
    select
       relid::regclass::text as relname,
       command,
       type,
       bytes_processed > 0 as has_bytes_processed,
       bytes_total > 0 as has_bytes_total,
       tuples_processed,
       tuples_excluded,
       tuples_skipped
      from pg_stat_progress_copy
      where pid = pg_backend_pid())
  select into report (to_jsonb(r)) as value
    from progress_data r;

  raise info 'progress: %', report.value::text;
  return new;
end;
$$ language plpgsql;

create trigger check_after_tab_progress_reporting
	after insert on tab_progress_reporting
	for each statement
	execute function notice_after_tab_progress_reporting();

-- Generate COPY FROM report with PIPE.
copy tab_progress_reporting from stdin;
sharon	25	(15,12)	1000	sam
sam	30	(10,5)	2000	bill
bill	20	(11,10)	1000	sharon
\.

-- Generate COPY FROM report with FILE, with some excluded tuples.
truncate tab_progress_reporting;
\set filename :abs_srcdir '/data/emp.data'
copy tab_progress_reporting from :'filename'
	where (salary < 2000);

-- Generate COPY FROM report with PIPE, with some skipped tuples.
copy tab_progress_reporting from stdin(on_error ignore);
sharon	x	(15,12)	x	sam
sharon	25	(15,12)	1000	sam
sharon	y	(15,12)	x	sam
\.

drop trigger check_after_tab_progress_reporting on tab_progress_reporting;
drop function notice_after_tab_progress_reporting();
drop table tab_progress_reporting;

-- Test header matching feature
create table header_copytest (
	a int,
	b int,
	c text
);
-- Make sure it works with dropped columns
alter table header_copytest drop column c;
alter table header_copytest add column c text;
copy header_copytest to stdout with (header match);
copy header_copytest from stdin with (header wrong_choice);
\.
-- works
copy header_copytest from stdin with (header match);
a	b	c
1	2	foo
\.
copy header_copytest (c, a, b) from stdin with (header match);
c	a	b
bar	3	4
\.
copy header_copytest from stdin with (header match, format csv);
a,b,c
5,6,baz
\.
-- errors
copy header_copytest (c, b, a) from stdin with (header match);
a	b	c
1	2	foo
\.
copy header_copytest from stdin with (header match);
a	b	\N
1	2	foo
\.
copy header_copytest from stdin with (header match);
a	b
1	2
\.
copy header_copytest from stdin with (header match);
a	b	c	d
1	2	foo	bar
\.
copy header_copytest from stdin with (header match);
a	b	d
1	2	foo
\.
SELECT * FROM header_copytest ORDER BY a;

-- Drop an extra column, in the middle of the existing set.
alter table header_copytest drop column b;
-- works
copy header_copytest (c, a) from stdin with (header match);
c	a
foo	7
\.
copy header_copytest (a, c) from stdin with (header match);
a	c
8	foo
\.
-- errors
copy header_copytest from stdin with (header match);
a	........pg.dropped.2........	c
1	2	foo
\.
copy header_copytest (a, c) from stdin with (header match);
a	c	b
1	foo	2
\.

SELECT * FROM header_copytest ORDER BY a;
drop table header_copytest;

-- test COPY with overlong column defaults
create temp table oversized_column_default (
    col1 varchar(5) DEFAULT 'more than 5 chars',
    col2 varchar(5));
-- normal COPY should work
copy oversized_column_default from stdin;
\.
-- error if the column is excluded
copy oversized_column_default (col2) from stdin;
\.
-- error if the DEFAULT option is given
copy oversized_column_default from stdin (default '');
\.
drop table oversized_column_default;


--
-- Create partitioned table that does not allow bulk insertions, to test bugs
-- related to the reuse of BulkInsertState across partitions (only done when
-- not using bulk insert).  Switching between partitions often makes it more
-- likely to encounter these bugs, so we just switch on roughly every insert
-- by having an even/odd number partition and inserting evenly distributed
-- data.
--
CREATE TABLE parted_si (
  id int not null,
  data text not null,
  -- prevent use of bulk insert by having a volatile function
  rand float8 not null default random()
)
PARTITION BY LIST((id % 2));

CREATE TABLE parted_si_p_even PARTITION OF parted_si FOR VALUES IN (0);
CREATE TABLE parted_si_p_odd PARTITION OF parted_si FOR VALUES IN (1);

-- Test that bulk relation extension handles reusing a single BulkInsertState
-- across partitions.  Without the fix applied, this reliably reproduces
-- #18130 unless shared_buffers is extremely small (preventing any use of bulk
-- relation extension). See
-- https://postgr.es/m/18130-7a86a7356a75209d%40postgresql.org
-- https://postgr.es/m/257696.1695670946%40sss.pgh.pa.us
\set filename :abs_srcdir '/data/desc.data'
COPY parted_si(id, data) FROM :'filename';

-- An earlier bug (see commit b1ecb9b3fcf) could end up using a buffer from
-- the wrong partition. This test is *not* guaranteed to trigger that bug, but
-- does so when shared_buffers is small enough.  To test if we encountered the
-- bug, check that the partition condition isn't violated.
SELECT tableoid::regclass, id % 2 = 0 is_even, count(*) from parted_si GROUP BY 1, 2 ORDER BY 1;

DROP TABLE parted_si;

-- ensure COPY FREEZE errors for foreign tables
begin;
create foreign data wrapper copytest_wrapper;
create server copytest_server foreign data wrapper copytest_wrapper;
create foreign table copytest_foreign_table (a int) server copytest_server;
copy copytest_foreign_table from stdin (freeze);
1
\.
rollback;

-- Tests for COPY TO with materialized views.
-- COPY TO should fail for an unpopulated materialized view
-- but succeed for a populated one.
CREATE MATERIALIZED VIEW copytest_mv AS SELECT 1 AS id WITH NO DATA;
COPY copytest_mv(id) TO stdout WITH (header);
REFRESH MATERIALIZED VIEW copytest_mv;
COPY copytest_mv(id) TO stdout WITH (header);
DROP MATERIALIZED VIEW copytest_mv;

-- Tests for COPY TO with partitioned tables.
-- The child table pp_2 has a different column order than the root table pp.
-- Check if COPY TO exports tuples as the root table's column order.
CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
COPY pp TO stdout(header);
DROP TABLE PP;

-- Check if COPY TO handles dropped columns in partitions.
CREATE TABLE pp_dropcol (id int, val int) PARTITION BY RANGE (id);
CREATE TABLE pp_dropcol_1 (dropme int, id int, val int);
ALTER TABLE pp_dropcol_1 DROP COLUMN dropme;
ALTER TABLE pp_dropcol ATTACH PARTITION pp_dropcol_1 FOR VALUES FROM (1) TO (10);
INSERT INTO pp_dropcol VALUES (1, 11), (2, 12);
COPY pp_dropcol TO stdout(header);
DROP TABLE pp_dropcol;