Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * copy.c
4 : * Implements the COPY utility command
5 : *
6 : * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
7 : * Portions Copyright (c) 1994, Regents of the University of California
8 : *
9 : *
10 : * IDENTIFICATION
11 : * src/backend/commands/copy.c
12 : *
13 : *-------------------------------------------------------------------------
14 : */
15 : #include "postgres.h"
16 :
17 : #include <ctype.h>
18 : #include <unistd.h>
19 : #include <sys/stat.h>
20 :
21 : #include "access/sysattr.h"
22 : #include "access/table.h"
23 : #include "access/xact.h"
24 : #include "catalog/pg_authid.h"
25 : #include "commands/copy.h"
26 : #include "commands/defrem.h"
27 : #include "executor/executor.h"
28 : #include "mb/pg_wchar.h"
29 : #include "miscadmin.h"
30 : #include "nodes/makefuncs.h"
31 : #include "optimizer/optimizer.h"
32 : #include "parser/parse_coerce.h"
33 : #include "parser/parse_collate.h"
34 : #include "parser/parse_expr.h"
35 : #include "parser/parse_relation.h"
36 : #include "utils/acl.h"
37 : #include "utils/builtins.h"
38 : #include "utils/lsyscache.h"
39 : #include "utils/rel.h"
40 : #include "utils/rls.h"
41 :
42 : /*
43 : * DoCopy executes the SQL COPY statement
44 : *
45 : * Either unload or reload contents of table <relation>, depending on <from>.
46 : * (<from> = true means we are inserting into the table.) In the "TO" case
47 : * we also support copying the output of an arbitrary SELECT, INSERT, UPDATE
48 : * or DELETE query.
49 : *
50 : * If <pipe> is false, transfer is between the table and the file named
51 : * <filename>. Otherwise, transfer is between the table and our regular
52 : * input/output stream. The latter could be either stdin/stdout or a
53 : * socket, depending on whether we're running under Postmaster control.
54 : *
55 : * Do not allow a Postgres user without the 'pg_read_server_files' or
56 : * 'pg_write_server_files' role to read from or write to a file.
57 : *
58 : * Do not allow the copy if user doesn't have proper permission to access
59 : * the table or the specifically requested columns.
60 : */
61 : void
62 16486 : DoCopy(ParseState *pstate, const CopyStmt *stmt,
63 : int stmt_location, int stmt_len,
64 : uint64 *processed)
65 : {
66 16486 : bool is_from = stmt->is_from;
67 16486 : bool pipe = (stmt->filename == NULL);
68 : Relation rel;
69 : Oid relid;
70 16486 : RawStmt *query = NULL;
71 16486 : Node *whereClause = NULL;
72 :
73 : /*
74 : * Disallow COPY to/from file or program except to users with the
75 : * appropriate role.
76 : */
77 16486 : if (!pipe)
78 : {
79 448 : if (stmt->is_program)
80 : {
81 0 : if (!has_privs_of_role(GetUserId(), ROLE_PG_EXECUTE_SERVER_PROGRAM))
82 0 : ereport(ERROR,
83 : (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
84 : errmsg("permission denied to COPY to or from an external program"),
85 : errdetail("Only roles with privileges of the \"%s\" role may COPY to or from an external program.",
86 : "pg_execute_server_program"),
87 : errhint("Anyone can COPY to stdout or from stdin. "
88 : "psql's \\copy command also works for anyone.")));
89 : }
90 : else
91 : {
92 448 : if (is_from && !has_privs_of_role(GetUserId(), ROLE_PG_READ_SERVER_FILES))
93 0 : ereport(ERROR,
94 : (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
95 : errmsg("permission denied to COPY from a file"),
96 : errdetail("Only roles with privileges of the \"%s\" role may COPY from a file.",
97 : "pg_read_server_files"),
98 : errhint("Anyone can COPY to stdout or from stdin. "
99 : "psql's \\copy command also works for anyone.")));
100 :
101 448 : if (!is_from && !has_privs_of_role(GetUserId(), ROLE_PG_WRITE_SERVER_FILES))
102 0 : ereport(ERROR,
103 : (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
104 : errmsg("permission denied to COPY to a file"),
105 : errdetail("Only roles with privileges of the \"%s\" role may COPY to a file.",
106 : "pg_write_server_files"),
107 : errhint("Anyone can COPY to stdout or from stdin. "
108 : "psql's \\copy command also works for anyone.")));
109 : }
110 : }
111 :
112 16486 : if (stmt->relation)
113 : {
114 15956 : LOCKMODE lockmode = is_from ? RowExclusiveLock : AccessShareLock;
115 : ParseNamespaceItem *nsitem;
116 : RTEPermissionInfo *perminfo;
117 : TupleDesc tupDesc;
118 : List *attnums;
119 : ListCell *cur;
120 :
121 : Assert(!stmt->query);
122 :
123 : /* Open and lock the relation, using the appropriate lock type. */
124 15956 : rel = table_openrv(stmt->relation, lockmode);
125 :
126 15954 : relid = RelationGetRelid(rel);
127 :
128 15954 : nsitem = addRangeTableEntryForRelation(pstate, rel, lockmode,
129 : NULL, false, false);
130 :
131 15954 : perminfo = nsitem->p_perminfo;
132 15954 : perminfo->requiredPerms = (is_from ? ACL_INSERT : ACL_SELECT);
133 :
134 15954 : if (stmt->whereClause)
135 : {
136 : /* add nsitem to query namespace */
137 48 : addNSItemToQuery(pstate, nsitem, false, true, true);
138 :
139 : /* Transform the raw expression tree */
140 48 : whereClause = transformExpr(pstate, stmt->whereClause, EXPR_KIND_COPY_WHERE);
141 :
142 : /* Make sure it yields a boolean result. */
143 18 : whereClause = coerce_to_boolean(pstate, whereClause, "WHERE");
144 :
145 : /* we have to fix its collations too */
146 18 : assign_expr_collations(pstate, whereClause);
147 :
148 18 : whereClause = eval_const_expressions(NULL, whereClause);
149 :
150 18 : whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
151 18 : whereClause = (Node *) make_ands_implicit((Expr *) whereClause);
152 : }
153 :
154 15924 : tupDesc = RelationGetDescr(rel);
155 15924 : attnums = CopyGetAttnums(tupDesc, rel, stmt->attlist);
156 73926 : foreach(cur, attnums)
157 : {
158 : int attno;
159 : Bitmapset **bms;
160 :
161 58086 : attno = lfirst_int(cur) - FirstLowInvalidHeapAttributeNumber;
162 58086 : bms = is_from ? &perminfo->insertedCols : &perminfo->selectedCols;
163 :
164 58086 : *bms = bms_add_member(*bms, attno);
165 : }
166 15840 : ExecCheckPermissions(pstate->p_rtable, list_make1(perminfo), true);
167 :
168 : /*
169 : * Permission check for row security policies.
170 : *
171 : * check_enable_rls will ereport(ERROR) if the user has requested
172 : * something invalid and will otherwise indicate if we should enable
173 : * RLS (returns RLS_ENABLED) or not for this COPY statement.
174 : *
175 : * If the relation has a row security policy and we are to apply it
176 : * then perform a "query" copy and allow the normal query processing
177 : * to handle the policies.
178 : *
179 : * If RLS is not enabled for this, then just fall through to the
180 : * normal non-filtering relation handling.
181 : */
182 15756 : if (check_enable_rls(relid, InvalidOid, false) == RLS_ENABLED)
183 : {
184 : SelectStmt *select;
185 : ColumnRef *cr;
186 : ResTarget *target;
187 : RangeVar *from;
188 60 : List *targetList = NIL;
189 :
190 60 : if (is_from)
191 6 : ereport(ERROR,
192 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
193 : errmsg("COPY FROM not supported with row-level security"),
194 : errhint("Use INSERT statements instead.")));
195 :
196 : /*
197 : * Build target list
198 : *
199 : * If no columns are specified in the attribute list of the COPY
200 : * command, then the target list is 'all' columns. Therefore, '*'
201 : * should be used as the target list for the resulting SELECT
202 : * statement.
203 : *
204 : * In the case that columns are specified in the attribute list,
205 : * create a ColumnRef and ResTarget for each column and add them
206 : * to the target list for the resulting SELECT statement.
207 : */
208 54 : if (!stmt->attlist)
209 : {
210 18 : cr = makeNode(ColumnRef);
211 18 : cr->fields = list_make1(makeNode(A_Star));
212 18 : cr->location = -1;
213 :
214 18 : target = makeNode(ResTarget);
215 18 : target->name = NULL;
216 18 : target->indirection = NIL;
217 18 : target->val = (Node *) cr;
218 18 : target->location = -1;
219 :
220 18 : targetList = list_make1(target);
221 : }
222 : else
223 : {
224 : ListCell *lc;
225 :
226 102 : foreach(lc, stmt->attlist)
227 : {
228 : /*
229 : * Build the ColumnRef for each column. The ColumnRef
230 : * 'fields' property is a String node that corresponds to
231 : * the column name respectively.
232 : */
233 66 : cr = makeNode(ColumnRef);
234 66 : cr->fields = list_make1(lfirst(lc));
235 66 : cr->location = -1;
236 :
237 : /* Build the ResTarget and add the ColumnRef to it. */
238 66 : target = makeNode(ResTarget);
239 66 : target->name = NULL;
240 66 : target->indirection = NIL;
241 66 : target->val = (Node *) cr;
242 66 : target->location = -1;
243 :
244 : /* Add each column to the SELECT statement's target list */
245 66 : targetList = lappend(targetList, target);
246 : }
247 : }
248 :
249 : /*
250 : * Build RangeVar for from clause, fully qualified based on the
251 : * relation which we have opened and locked. Use "ONLY" so that
252 : * COPY retrieves rows from only the target table not any
253 : * inheritance children, the same as when RLS doesn't apply.
254 : */
255 54 : from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
256 54 : pstrdup(RelationGetRelationName(rel)),
257 : -1);
258 54 : from->inh = false; /* apply ONLY */
259 :
260 : /* Build query */
261 54 : select = makeNode(SelectStmt);
262 54 : select->targetList = targetList;
263 54 : select->fromClause = list_make1(from);
264 :
265 54 : query = makeNode(RawStmt);
266 54 : query->stmt = (Node *) select;
267 54 : query->stmt_location = stmt_location;
268 54 : query->stmt_len = stmt_len;
269 :
270 : /*
271 : * Close the relation for now, but keep the lock on it to prevent
272 : * changes between now and when we start the query-based COPY.
273 : *
274 : * We'll reopen it later as part of the query-based COPY.
275 : */
276 54 : table_close(rel, NoLock);
277 54 : rel = NULL;
278 : }
279 : }
280 : else
281 : {
282 : Assert(stmt->query);
283 :
284 530 : query = makeNode(RawStmt);
285 530 : query->stmt = stmt->query;
286 530 : query->stmt_location = stmt_location;
287 530 : query->stmt_len = stmt_len;
288 :
289 530 : relid = InvalidOid;
290 530 : rel = NULL;
291 : }
292 :
293 16262 : if (is_from)
294 : {
295 : CopyFromState cstate;
296 :
297 : Assert(rel);
298 :
299 : /* check read-only transaction and parallel mode */
300 2992 : if (XactReadOnly && !rel->rd_islocaltemp)
301 0 : PreventCommandIfReadOnly("COPY FROM");
302 :
303 2992 : cstate = BeginCopyFrom(pstate, rel, whereClause,
304 2992 : stmt->filename, stmt->is_program,
305 2992 : NULL, stmt->attlist, stmt->options);
306 2728 : *processed = CopyFrom(cstate); /* copy from file to database */
307 2496 : EndCopyFrom(cstate);
308 : }
309 : else
310 : {
311 : CopyToState cstate;
312 :
313 13270 : cstate = BeginCopyTo(pstate, rel, query, relid,
314 13270 : stmt->filename, stmt->is_program,
315 13270 : NULL, stmt->attlist, stmt->options);
316 13070 : *processed = DoCopyTo(cstate); /* copy from database to file */
317 13068 : EndCopyTo(cstate);
318 : }
319 :
320 15564 : if (rel != NULL)
321 15120 : table_close(rel, NoLock);
322 15564 : }
323 :
324 : /*
325 : * Extract the CopyFormatOptions.header_line value from a DefElem.
326 : *
327 : * Parses the HEADER option for COPY, which can be a boolean, a non-negative
328 : * integer (number of lines to skip), or the special value "match".
329 : */
330 : static int
331 222 : defGetCopyHeaderOption(DefElem *def, bool is_from)
332 : {
333 : /*
334 : * If no parameter value given, assume "true" is meant.
335 : */
336 222 : if (def->arg == NULL)
337 24 : return COPY_HEADER_TRUE;
338 :
339 : /*
340 : * Allow 0, 1, "true", "false", "on", "off", a non-negative integer, or
341 : * "match".
342 : */
343 198 : switch (nodeTag(def->arg))
344 : {
345 30 : case T_Integer:
346 : {
347 30 : int ival = intVal(def->arg);
348 :
349 30 : if (ival < 0)
350 6 : ereport(ERROR,
351 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
352 : errmsg("a negative integer value cannot be "
353 : "specified for %s", def->defname)));
354 :
355 24 : if (!is_from && ival > 1)
356 6 : ereport(ERROR,
357 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
358 : errmsg("cannot use multi-line header in COPY TO")));
359 :
360 18 : return ival;
361 : }
362 : break;
363 168 : default:
364 : {
365 168 : char *sval = defGetString(def);
366 :
367 : /*
368 : * The set of strings accepted here should match up with the
369 : * grammar's opt_boolean_or_string production.
370 : */
371 168 : if (pg_strcasecmp(sval, "true") == 0)
372 64 : return COPY_HEADER_TRUE;
373 104 : if (pg_strcasecmp(sval, "false") == 0)
374 0 : return COPY_HEADER_FALSE;
375 104 : if (pg_strcasecmp(sval, "on") == 0)
376 0 : return COPY_HEADER_TRUE;
377 104 : if (pg_strcasecmp(sval, "off") == 0)
378 6 : return COPY_HEADER_FALSE;
379 98 : if (pg_strcasecmp(sval, "match") == 0)
380 : {
381 86 : if (!is_from)
382 6 : ereport(ERROR,
383 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
384 : errmsg("cannot use \"%s\" with HEADER in COPY TO",
385 : sval)));
386 80 : return COPY_HEADER_MATCH;
387 : }
388 : }
389 12 : break;
390 : }
391 12 : ereport(ERROR,
392 : (errcode(ERRCODE_SYNTAX_ERROR),
393 : errmsg("%s requires a Boolean value, a non-negative integer, "
394 : "or the string \"match\"",
395 : def->defname)));
396 : return COPY_HEADER_FALSE; /* keep compiler quiet */
397 : }
398 :
399 : /*
400 : * Extract a CopyOnErrorChoice value from a DefElem.
401 : */
402 : static CopyOnErrorChoice
403 114 : defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
404 : {
405 114 : char *sval = defGetString(def);
406 :
407 114 : if (!is_from)
408 6 : ereport(ERROR,
409 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
410 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
411 : second %s is a COPY with direction, e.g. COPY TO */
412 : errmsg("COPY %s cannot be used with %s", "ON_ERROR", "COPY TO"),
413 : parser_errposition(pstate, def->location)));
414 :
415 : /*
416 : * Allow "stop", or "ignore" values.
417 : */
418 108 : if (pg_strcasecmp(sval, "stop") == 0)
419 6 : return COPY_ON_ERROR_STOP;
420 102 : if (pg_strcasecmp(sval, "ignore") == 0)
421 94 : return COPY_ON_ERROR_IGNORE;
422 :
423 8 : ereport(ERROR,
424 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
425 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR */
426 : errmsg("COPY %s \"%s\" not recognized", "ON_ERROR", sval),
427 : parser_errposition(pstate, def->location)));
428 : return COPY_ON_ERROR_STOP; /* keep compiler quiet */
429 : }
430 :
431 : /*
432 : * Extract REJECT_LIMIT value from a DefElem.
433 : *
434 : * REJECT_LIMIT can be specified in two ways: as an int64 for the COPY command
435 : * option or as a single-quoted string for the foreign table option using
436 : * file_fdw. Therefore this function needs to handle both formats.
437 : */
438 : static int64
439 38 : defGetCopyRejectLimitOption(DefElem *def)
440 : {
441 : int64 reject_limit;
442 :
443 38 : if (def->arg == NULL)
444 0 : ereport(ERROR,
445 : (errcode(ERRCODE_SYNTAX_ERROR),
446 : errmsg("%s requires a numeric value",
447 : def->defname)));
448 38 : else if (nodeTag(def->arg) == T_String)
449 14 : reject_limit = pg_strtoint64(strVal(def->arg));
450 : else
451 24 : reject_limit = defGetInt64(def);
452 :
453 38 : if (reject_limit <= 0)
454 8 : ereport(ERROR,
455 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
456 : errmsg("REJECT_LIMIT (%" PRId64 ") must be greater than zero",
457 : reject_limit)));
458 :
459 30 : return reject_limit;
460 : }
461 :
462 : /*
463 : * Extract a CopyLogVerbosityChoice value from a DefElem.
464 : */
465 : static CopyLogVerbosityChoice
466 46 : defGetCopyLogVerbosityChoice(DefElem *def, ParseState *pstate)
467 : {
468 : char *sval;
469 :
470 : /*
471 : * Allow "silent", "default", or "verbose" values.
472 : */
473 46 : sval = defGetString(def);
474 46 : if (pg_strcasecmp(sval, "silent") == 0)
475 20 : return COPY_LOG_VERBOSITY_SILENT;
476 26 : if (pg_strcasecmp(sval, "default") == 0)
477 6 : return COPY_LOG_VERBOSITY_DEFAULT;
478 20 : if (pg_strcasecmp(sval, "verbose") == 0)
479 12 : return COPY_LOG_VERBOSITY_VERBOSE;
480 :
481 8 : ereport(ERROR,
482 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
483 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR */
484 : errmsg("COPY %s \"%s\" not recognized", "LOG_VERBOSITY", sval),
485 : parser_errposition(pstate, def->location)));
486 : return COPY_LOG_VERBOSITY_DEFAULT; /* keep compiler quiet */
487 : }
488 :
489 : /*
490 : * Process the statement option list for COPY.
491 : *
492 : * Scan the options list (a list of DefElem) and transpose the information
493 : * into *opts_out, applying appropriate error checking.
494 : *
495 : * If 'opts_out' is not NULL, it is assumed to be filled with zeroes initially.
496 : *
497 : * This is exported so that external users of the COPY API can sanity-check
498 : * a list of options. In that usage, 'opts_out' can be passed as NULL and
499 : * the collected data is just leaked until CurrentMemoryContext is reset.
500 : *
501 : * Note that additional checking, such as whether column names listed in FORCE
502 : * QUOTE actually exist, has to be applied later. This just checks for
503 : * self-consistency of the options list.
504 : */
505 : void
506 16804 : ProcessCopyOptions(ParseState *pstate,
507 : CopyFormatOptions *opts_out,
508 : bool is_from,
509 : List *options)
510 : {
511 16804 : bool format_specified = false;
512 16804 : bool freeze_specified = false;
513 16804 : bool header_specified = false;
514 16804 : bool on_error_specified = false;
515 16804 : bool log_verbosity_specified = false;
516 16804 : bool reject_limit_specified = false;
517 : ListCell *option;
518 :
519 : /* Support external use for option sanity checking */
520 16804 : if (opts_out == NULL)
521 104 : opts_out = (CopyFormatOptions *) palloc0(sizeof(CopyFormatOptions));
522 :
523 16804 : opts_out->file_encoding = -1;
524 :
525 : /* Extract options from the statement node tree */
526 18856 : foreach(option, options)
527 : {
528 2210 : DefElem *defel = lfirst_node(DefElem, option);
529 :
530 2210 : if (strcmp(defel->defname, "format") == 0)
531 : {
532 656 : char *fmt = defGetString(defel);
533 :
534 656 : if (format_specified)
535 6 : errorConflictingDefElem(defel, pstate);
536 650 : format_specified = true;
537 650 : if (strcmp(fmt, "text") == 0)
538 : /* default format */ ;
539 574 : else if (strcmp(fmt, "csv") == 0)
540 502 : opts_out->csv_mode = true;
541 72 : else if (strcmp(fmt, "binary") == 0)
542 70 : opts_out->binary = true;
543 : else
544 2 : ereport(ERROR,
545 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
546 : errmsg("COPY format \"%s\" not recognized", fmt),
547 : parser_errposition(pstate, defel->location)));
548 : }
549 1554 : else if (strcmp(defel->defname, "freeze") == 0)
550 : {
551 84 : if (freeze_specified)
552 6 : errorConflictingDefElem(defel, pstate);
553 78 : freeze_specified = true;
554 78 : opts_out->freeze = defGetBoolean(defel);
555 : }
556 1470 : else if (strcmp(defel->defname, "delimiter") == 0)
557 : {
558 284 : if (opts_out->delim)
559 6 : errorConflictingDefElem(defel, pstate);
560 278 : opts_out->delim = defGetString(defel);
561 : }
562 1186 : else if (strcmp(defel->defname, "null") == 0)
563 : {
564 144 : if (opts_out->null_print)
565 6 : errorConflictingDefElem(defel, pstate);
566 138 : opts_out->null_print = defGetString(defel);
567 : }
568 1042 : else if (strcmp(defel->defname, "default") == 0)
569 : {
570 90 : if (opts_out->default_print)
571 0 : errorConflictingDefElem(defel, pstate);
572 90 : opts_out->default_print = defGetString(defel);
573 : }
574 952 : else if (strcmp(defel->defname, "header") == 0)
575 : {
576 228 : if (header_specified)
577 6 : errorConflictingDefElem(defel, pstate);
578 222 : header_specified = true;
579 222 : opts_out->header_line = defGetCopyHeaderOption(defel, is_from);
580 : }
581 724 : else if (strcmp(defel->defname, "quote") == 0)
582 : {
583 102 : if (opts_out->quote)
584 6 : errorConflictingDefElem(defel, pstate);
585 96 : opts_out->quote = defGetString(defel);
586 : }
587 622 : else if (strcmp(defel->defname, "escape") == 0)
588 : {
589 94 : if (opts_out->escape)
590 6 : errorConflictingDefElem(defel, pstate);
591 88 : opts_out->escape = defGetString(defel);
592 : }
593 528 : else if (strcmp(defel->defname, "force_quote") == 0)
594 : {
595 78 : if (opts_out->force_quote || opts_out->force_quote_all)
596 6 : errorConflictingDefElem(defel, pstate);
597 72 : if (defel->arg && IsA(defel->arg, A_Star))
598 30 : opts_out->force_quote_all = true;
599 42 : else if (defel->arg && IsA(defel->arg, List))
600 42 : opts_out->force_quote = castNode(List, defel->arg);
601 : else
602 0 : ereport(ERROR,
603 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
604 : errmsg("argument to option \"%s\" must be a list of column names",
605 : defel->defname),
606 : parser_errposition(pstate, defel->location)));
607 : }
608 450 : else if (strcmp(defel->defname, "force_not_null") == 0)
609 : {
610 88 : if (opts_out->force_notnull || opts_out->force_notnull_all)
611 12 : errorConflictingDefElem(defel, pstate);
612 76 : if (defel->arg && IsA(defel->arg, A_Star))
613 30 : opts_out->force_notnull_all = true;
614 46 : else if (defel->arg && IsA(defel->arg, List))
615 46 : opts_out->force_notnull = castNode(List, defel->arg);
616 : else
617 0 : ereport(ERROR,
618 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
619 : errmsg("argument to option \"%s\" must be a list of column names",
620 : defel->defname),
621 : parser_errposition(pstate, defel->location)));
622 : }
623 362 : else if (strcmp(defel->defname, "force_null") == 0)
624 : {
625 88 : if (opts_out->force_null || opts_out->force_null_all)
626 12 : errorConflictingDefElem(defel, pstate);
627 76 : if (defel->arg && IsA(defel->arg, A_Star))
628 30 : opts_out->force_null_all = true;
629 46 : else if (defel->arg && IsA(defel->arg, List))
630 46 : opts_out->force_null = castNode(List, defel->arg);
631 : else
632 0 : ereport(ERROR,
633 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
634 : errmsg("argument to option \"%s\" must be a list of column names",
635 : defel->defname),
636 : parser_errposition(pstate, defel->location)));
637 : }
638 274 : else if (strcmp(defel->defname, "convert_selectively") == 0)
639 : {
640 : /*
641 : * Undocumented, not-accessible-from-SQL option: convert only the
642 : * named columns to binary form, storing the rest as NULLs. It's
643 : * allowed for the column list to be NIL.
644 : */
645 16 : if (opts_out->convert_selectively)
646 6 : errorConflictingDefElem(defel, pstate);
647 10 : opts_out->convert_selectively = true;
648 10 : if (defel->arg == NULL || IsA(defel->arg, List))
649 10 : opts_out->convert_select = castNode(List, defel->arg);
650 : else
651 0 : ereport(ERROR,
652 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
653 : errmsg("argument to option \"%s\" must be a list of column names",
654 : defel->defname),
655 : parser_errposition(pstate, defel->location)));
656 : }
657 258 : else if (strcmp(defel->defname, "encoding") == 0)
658 : {
659 48 : if (opts_out->file_encoding >= 0)
660 6 : errorConflictingDefElem(defel, pstate);
661 42 : opts_out->file_encoding = pg_char_to_encoding(defGetString(defel));
662 42 : if (opts_out->file_encoding < 0)
663 0 : ereport(ERROR,
664 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
665 : errmsg("argument to option \"%s\" must be a valid encoding name",
666 : defel->defname),
667 : parser_errposition(pstate, defel->location)));
668 : }
669 210 : else if (strcmp(defel->defname, "on_error") == 0)
670 : {
671 120 : if (on_error_specified)
672 6 : errorConflictingDefElem(defel, pstate);
673 114 : on_error_specified = true;
674 114 : opts_out->on_error = defGetCopyOnErrorChoice(defel, pstate, is_from);
675 : }
676 90 : else if (strcmp(defel->defname, "log_verbosity") == 0)
677 : {
678 52 : if (log_verbosity_specified)
679 6 : errorConflictingDefElem(defel, pstate);
680 46 : log_verbosity_specified = true;
681 46 : opts_out->log_verbosity = defGetCopyLogVerbosityChoice(defel, pstate);
682 : }
683 38 : else if (strcmp(defel->defname, "reject_limit") == 0)
684 : {
685 38 : if (reject_limit_specified)
686 0 : errorConflictingDefElem(defel, pstate);
687 38 : reject_limit_specified = true;
688 38 : opts_out->reject_limit = defGetCopyRejectLimitOption(defel);
689 : }
690 : else
691 0 : ereport(ERROR,
692 : (errcode(ERRCODE_SYNTAX_ERROR),
693 : errmsg("option \"%s\" not recognized",
694 : defel->defname),
695 : parser_errposition(pstate, defel->location)));
696 : }
697 :
698 : /*
699 : * Check for incompatible options (must do these three before inserting
700 : * defaults)
701 : */
702 16646 : if (opts_out->binary && opts_out->delim)
703 6 : ereport(ERROR,
704 : (errcode(ERRCODE_SYNTAX_ERROR),
705 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
706 : errmsg("cannot specify %s in BINARY mode", "DELIMITER")));
707 :
708 16640 : if (opts_out->binary && opts_out->null_print)
709 6 : ereport(ERROR,
710 : (errcode(ERRCODE_SYNTAX_ERROR),
711 : errmsg("cannot specify %s in BINARY mode", "NULL")));
712 :
713 16634 : if (opts_out->binary && opts_out->default_print)
714 6 : ereport(ERROR,
715 : (errcode(ERRCODE_SYNTAX_ERROR),
716 : errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
717 :
718 : /* Set defaults for omitted options */
719 16628 : if (!opts_out->delim)
720 16362 : opts_out->delim = opts_out->csv_mode ? "," : "\t";
721 :
722 16628 : if (!opts_out->null_print)
723 16502 : opts_out->null_print = opts_out->csv_mode ? "" : "\\N";
724 16628 : opts_out->null_print_len = strlen(opts_out->null_print);
725 :
726 16628 : if (opts_out->csv_mode)
727 : {
728 484 : if (!opts_out->quote)
729 398 : opts_out->quote = "\"";
730 484 : if (!opts_out->escape)
731 408 : opts_out->escape = opts_out->quote;
732 : }
733 :
734 : /* Only single-byte delimiter strings are supported. */
735 16628 : if (strlen(opts_out->delim) != 1)
736 2 : ereport(ERROR,
737 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
738 : errmsg("COPY delimiter must be a single one-byte character")));
739 :
740 : /* Disallow end-of-line characters */
741 16626 : if (strchr(opts_out->delim, '\r') != NULL ||
742 16626 : strchr(opts_out->delim, '\n') != NULL)
743 2 : ereport(ERROR,
744 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
745 : errmsg("COPY delimiter cannot be newline or carriage return")));
746 :
747 16624 : if (strchr(opts_out->null_print, '\r') != NULL ||
748 16624 : strchr(opts_out->null_print, '\n') != NULL)
749 2 : ereport(ERROR,
750 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
751 : errmsg("COPY null representation cannot use newline or carriage return")));
752 :
753 16622 : if (opts_out->default_print)
754 : {
755 84 : opts_out->default_print_len = strlen(opts_out->default_print);
756 :
757 84 : if (strchr(opts_out->default_print, '\r') != NULL ||
758 78 : strchr(opts_out->default_print, '\n') != NULL)
759 12 : ereport(ERROR,
760 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
761 : errmsg("COPY default representation cannot use newline or carriage return")));
762 : }
763 :
764 : /*
765 : * Disallow unsafe delimiter characters in non-CSV mode. We can't allow
766 : * backslash because it would be ambiguous. We can't allow the other
767 : * cases because data characters matching the delimiter must be
768 : * backslashed, and certain backslash combinations are interpreted
769 : * non-literally by COPY IN. Disallowing all lower case ASCII letters is
770 : * more than strictly necessary, but seems best for consistency and
771 : * future-proofing. Likewise we disallow all digits though only octal
772 : * digits are actually dangerous.
773 : */
774 16610 : if (!opts_out->csv_mode &&
775 16132 : strchr("\\.abcdefghijklmnopqrstuvwxyz0123456789",
776 16132 : opts_out->delim[0]) != NULL)
777 10 : ereport(ERROR,
778 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
779 : errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
780 :
781 : /* Check header */
782 16600 : if (opts_out->binary && opts_out->header_line != COPY_HEADER_FALSE)
783 2 : ereport(ERROR,
784 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
785 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
786 : errmsg("cannot specify %s in BINARY mode", "HEADER")));
787 :
788 : /* Check quote */
789 16598 : if (!opts_out->csv_mode && opts_out->quote != NULL)
790 4 : ereport(ERROR,
791 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
792 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
793 : errmsg("COPY %s requires CSV mode", "QUOTE")));
794 :
795 16594 : if (opts_out->csv_mode && strlen(opts_out->quote) != 1)
796 2 : ereport(ERROR,
797 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
798 : errmsg("COPY quote must be a single one-byte character")));
799 :
800 16592 : if (opts_out->csv_mode && opts_out->delim[0] == opts_out->quote[0])
801 2 : ereport(ERROR,
802 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
803 : errmsg("COPY delimiter and quote must be different")));
804 :
805 : /* Check escape */
806 16590 : if (!opts_out->csv_mode && opts_out->escape != NULL)
807 6 : ereport(ERROR,
808 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
809 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
810 : errmsg("COPY %s requires CSV mode", "ESCAPE")));
811 :
812 16584 : if (opts_out->csv_mode && strlen(opts_out->escape) != 1)
813 2 : ereport(ERROR,
814 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
815 : errmsg("COPY escape must be a single one-byte character")));
816 :
817 : /* Check force_quote */
818 16582 : if (!opts_out->csv_mode && (opts_out->force_quote || opts_out->force_quote_all))
819 12 : ereport(ERROR,
820 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
821 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
822 : errmsg("COPY %s requires CSV mode", "FORCE_QUOTE")));
823 16570 : if ((opts_out->force_quote || opts_out->force_quote_all) && is_from)
824 12 : ereport(ERROR,
825 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
826 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
827 : second %s is a COPY with direction, e.g. COPY TO */
828 : errmsg("COPY %s cannot be used with %s", "FORCE_QUOTE",
829 : "COPY FROM")));
830 :
831 : /* Check force_notnull */
832 16558 : if (!opts_out->csv_mode && (opts_out->force_notnull != NIL ||
833 16090 : opts_out->force_notnull_all))
834 14 : ereport(ERROR,
835 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
836 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
837 : errmsg("COPY %s requires CSV mode", "FORCE_NOT_NULL")));
838 16544 : if ((opts_out->force_notnull != NIL || opts_out->force_notnull_all) &&
839 50 : !is_from)
840 12 : ereport(ERROR,
841 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
842 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
843 : second %s is a COPY with direction, e.g. COPY TO */
844 : errmsg("COPY %s cannot be used with %s", "FORCE_NOT_NULL",
845 : "COPY TO")));
846 :
847 : /* Check force_null */
848 16532 : if (!opts_out->csv_mode && (opts_out->force_null != NIL ||
849 16078 : opts_out->force_null_all))
850 12 : ereport(ERROR,
851 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
852 : /*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
853 : errmsg("COPY %s requires CSV mode", "FORCE_NULL")));
854 :
855 16520 : if ((opts_out->force_null != NIL || opts_out->force_null_all) &&
856 50 : !is_from)
857 12 : ereport(ERROR,
858 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
859 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
860 : second %s is a COPY with direction, e.g. COPY TO */
861 : errmsg("COPY %s cannot be used with %s", "FORCE_NULL",
862 : "COPY TO")));
863 :
864 : /* Don't allow the delimiter to appear in the null string. */
865 16508 : if (strchr(opts_out->null_print, opts_out->delim[0]) != NULL)
866 2 : ereport(ERROR,
867 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
868 : /*- translator: %s is the name of a COPY option, e.g. NULL */
869 : errmsg("COPY delimiter character must not appear in the %s specification",
870 : "NULL")));
871 :
872 : /* Don't allow the CSV quote char to appear in the null string. */
873 16506 : if (opts_out->csv_mode &&
874 434 : strchr(opts_out->null_print, opts_out->quote[0]) != NULL)
875 2 : ereport(ERROR,
876 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
877 : /*- translator: %s is the name of a COPY option, e.g. NULL */
878 : errmsg("CSV quote character must not appear in the %s specification",
879 : "NULL")));
880 :
881 : /* Check freeze */
882 16504 : if (opts_out->freeze && !is_from)
883 0 : ereport(ERROR,
884 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
885 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
886 : second %s is a COPY with direction, e.g. COPY TO */
887 : errmsg("COPY %s cannot be used with %s", "FREEZE",
888 : "COPY TO")));
889 :
890 16504 : if (opts_out->default_print)
891 : {
892 72 : if (!is_from)
893 6 : ereport(ERROR,
894 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
895 : /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR,
896 : second %s is a COPY with direction, e.g. COPY TO */
897 : errmsg("COPY %s cannot be used with %s", "DEFAULT",
898 : "COPY TO")));
899 :
900 : /* Don't allow the delimiter to appear in the default string. */
901 66 : if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
902 6 : ereport(ERROR,
903 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
904 : /*- translator: %s is the name of a COPY option, e.g. NULL */
905 : errmsg("COPY delimiter character must not appear in the %s specification",
906 : "DEFAULT")));
907 :
908 : /* Don't allow the CSV quote char to appear in the default string. */
909 60 : if (opts_out->csv_mode &&
910 30 : strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
911 6 : ereport(ERROR,
912 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
913 : /*- translator: %s is the name of a COPY option, e.g. NULL */
914 : errmsg("CSV quote character must not appear in the %s specification",
915 : "DEFAULT")));
916 :
917 : /* Don't allow the NULL and DEFAULT string to be the same */
918 54 : if (opts_out->null_print_len == opts_out->default_print_len &&
919 24 : strncmp(opts_out->null_print, opts_out->default_print,
920 24 : opts_out->null_print_len) == 0)
921 6 : ereport(ERROR,
922 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
923 : errmsg("NULL specification and DEFAULT specification cannot be the same")));
924 : }
925 : /* Check on_error */
926 16480 : if (opts_out->binary && opts_out->on_error != COPY_ON_ERROR_STOP)
927 8 : ereport(ERROR,
928 : (errcode(ERRCODE_SYNTAX_ERROR),
929 : errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
930 :
931 16472 : if (opts_out->reject_limit && !opts_out->on_error)
932 8 : ereport(ERROR,
933 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
934 : /*- translator: first and second %s are the names of COPY option, e.g.
935 : * ON_ERROR, third is the value of the COPY option, e.g. IGNORE */
936 : errmsg("COPY %s requires %s to be set to %s",
937 : "REJECT_LIMIT", "ON_ERROR", "IGNORE")));
938 16464 : }
939 :
940 : /*
941 : * CopyGetAttnums - build an integer list of attnums to be copied
942 : *
943 : * The input attnamelist is either the user-specified column list,
944 : * or NIL if there was none (in which case we want all the non-dropped
945 : * columns).
946 : *
947 : * We don't include generated columns in the generated full list and we don't
948 : * allow them to be specified explicitly. They don't make sense for COPY
949 : * FROM, but we could possibly allow them for COPY TO. But this way it's at
950 : * least ensured that whatever we copy out can be copied back in.
951 : *
952 : * rel can be NULL ... it's only used for error reports.
953 : */
954 : List *
955 32280 : CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
956 : {
957 32280 : List *attnums = NIL;
958 :
959 32280 : if (attnamelist == NIL)
960 : {
961 : /* Generate default column list */
962 3834 : int attr_count = tupDesc->natts;
963 : int i;
964 :
965 13184 : for (i = 0; i < attr_count; i++)
966 : {
967 9350 : CompactAttribute *attr = TupleDescCompactAttr(tupDesc, i);
968 :
969 9350 : if (attr->attisdropped || attr->attgenerated)
970 318 : continue;
971 9032 : attnums = lappend_int(attnums, i + 1);
972 : }
973 : }
974 : else
975 : {
976 : /* Validate the user-supplied list and extract attnums */
977 : ListCell *l;
978 :
979 135984 : foreach(l, attnamelist)
980 : {
981 107622 : char *name = strVal(lfirst(l));
982 : int attnum;
983 : int i;
984 :
985 : /* Lookup column name */
986 107622 : attnum = InvalidAttrNumber;
987 19759844 : for (i = 0; i < tupDesc->natts; i++)
988 : {
989 19759814 : Form_pg_attribute att = TupleDescAttr(tupDesc, i);
990 :
991 19759814 : if (att->attisdropped)
992 1068 : continue;
993 19758746 : if (namestrcmp(&(att->attname), name) == 0)
994 : {
995 107592 : if (att->attgenerated)
996 48 : ereport(ERROR,
997 : (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
998 : errmsg("column \"%s\" is a generated column",
999 : name),
1000 : errdetail("Generated columns cannot be used in COPY.")));
1001 107544 : attnum = att->attnum;
1002 107544 : break;
1003 : }
1004 : }
1005 107574 : if (attnum == InvalidAttrNumber)
1006 : {
1007 30 : if (rel != NULL)
1008 30 : ereport(ERROR,
1009 : (errcode(ERRCODE_UNDEFINED_COLUMN),
1010 : errmsg("column \"%s\" of relation \"%s\" does not exist",
1011 : name, RelationGetRelationName(rel))));
1012 : else
1013 0 : ereport(ERROR,
1014 : (errcode(ERRCODE_UNDEFINED_COLUMN),
1015 : errmsg("column \"%s\" does not exist",
1016 : name)));
1017 : }
1018 : /* Check for duplicates */
1019 107544 : if (list_member_int(attnums, attnum))
1020 6 : ereport(ERROR,
1021 : (errcode(ERRCODE_DUPLICATE_COLUMN),
1022 : errmsg("column \"%s\" specified more than once",
1023 : name)));
1024 107538 : attnums = lappend_int(attnums, attnum);
1025 : }
1026 : }
1027 :
1028 32196 : return attnums;
1029 : }
|