Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbcaabc5

Browse files
committed
Depending on my interpreting (and programming) skills, this might solve
anywhere from zero to two TODO items.* Allow flag to control COPY input/output of NULLsI got this:COPY table .... [ WITH NULL AS 'string' ]which does what you'd expect. The default is \N, otherwise you can useempty strings, etc. On Copy In this acts like a filter: every data itemthat looks like 'string' becomes a NULL. Pretty straightforward.This also seems to be related to* Make postgres user have a password by defaultIf I recall this discussion correctly, the problem was actually that thedefault password for the postgres (or any) user is in fact "\N", becauseof the way copy is used. With this change, the file pg_pwd is copied outwith nulls as empty strings, so if someone doesn't have a password, thepassword is just '', which one would expect from a new account. I don'tthink anyone really wants a hard-coded default password.Peter Eisentraut Sernanders väg 10:115
1 parenta82f9ff commitbcaabc5

File tree

7 files changed

+67
-30
lines changed

7 files changed

+67
-30
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 27 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.10 1999/10/29 23:52:20 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.11 1999/12/14 00:08:12 momjian Exp $
33
Postgres documentation
44
-->
55

@@ -20,15 +20,17 @@ Postgres documentation
2020
</refnamediv>
2121
<refsynopsisdiv>
2222
<refsynopsisdivinfo>
23-
<date>1999-07-20</date>
23+
<date>1999-12-11</date>
2424
</refsynopsisdivinfo>
2525
<synopsis>
2626
COPY [ BINARY ] <replaceable class="parameter">table</replaceable> [ WITH OIDS ]
2727
FROM { '<replaceable class="parameter">filename</replaceable>' | <filename>stdin</filename> }
2828
[ [USING] DELIMITERS '<replaceable class="parameter">delimiter</replaceable>' ]
29+
[ WITH NULL AS '<replaceable class="parameter">null string</replaceable>' ]
2930
COPY [ BINARY ] <replaceable class="parameter">table</replaceable> [ WITH OIDS ]
3031
TO { '<replaceable class="parameter">filename</replaceable>' | <filename>stdout</filename> }
3132
[ [USING] DELIMITERS '<replaceable class="parameter">delimiter</replaceable>' ]
33+
[ WITH NULL AS '<replaceable class="parameter">null string</replaceable>' ]
3234
</synopsis>
3335

3436
<refsect2 id="R2-SQL-COPY-1">
@@ -104,6 +106,25 @@ COPY [ BINARY ] <replaceable class="parameter">table</replaceable> [ WITH OIDS ]
104106
</para>
105107
</listitem>
106108
</varlistentry>
109+
110+
<varlistentry>
111+
<term><replaceable class="parameter">null print</replaceable></term>
112+
<listitem>
113+
<para>
114+
A string to represent NULL values. The default is
115+
<quote><literal>\N</literal></quote> (backslash-N), for historical
116+
reasons. You might prefer an empty string, for example.
117+
</para>
118+
<note>
119+
<para>
120+
On a copy in, any data item that matches this string will be stored as
121+
a NULL value, so you should make sure that you use the same string
122+
as you used on copy out.
123+
</para>
124+
</hote>
125+
</listitem>
126+
</varlistentry>
127+
107128
</variablelist>
108129
</para>
109130
</refsect2>
@@ -287,15 +308,16 @@ ERROR: <replaceable>reason</replaceable>
287308
encountered before this special end-of-file pattern is found.
288309
</para>
289310
<para>
290-
The backslash character has other special meanings.NULL attributes are
291-
represented as "\N". A literal backslashcharacter is represented as two
311+
The backslash character has other special meanings.A literal backslash
312+
character is represented as two
292313
consecutive backslashes ("\\"). A literal tab character is represented
293314
as a backslash and a tab. A literal newline character is
294315
represented as a backslash and a newline. When loading text data
295316
not generated by <acronym>Postgres</acronym>,
296317
you will need to convert backslash
297318
characters ("\") to double-backslashes ("\\") to ensure that they are loaded
298-
properly.
319+
properly. (The sequence "\N" will always be interpreted as a backslash and
320+
an "N", for compatibility. The more general solution is "\\N".)
299321
</para>
300322
</refsect2>
301323

‎src/backend/commands/copy.c

Lines changed: 26 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
*
77
*
88
* IDENTIFICATION
9-
* $Header: /cvsroot/pgsql/src/backend/commands/copy.c,v 1.92 1999/11/27 21:52:53 tgl Exp $
9+
* $Header: /cvsroot/pgsql/src/backend/commands/copy.c,v 1.93 1999/12/14 00:08:13 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -43,8 +43,8 @@
4343

4444

4545
/* non-export function prototypes */
46-
staticvoidCopyTo(Relationrel,boolbinary,booloids,FILE*fp,char*delim);
47-
staticvoidCopyFrom(Relationrel,boolbinary,booloids,FILE*fp,char*delim);
46+
staticvoidCopyTo(Relationrel,boolbinary,booloids,FILE*fp,char*delim,char*null_print);
47+
staticvoidCopyFrom(Relationrel,boolbinary,booloids,FILE*fp,char*delim,char*null_print);
4848
staticOidGetOutputFunction(Oidtype);
4949
staticOidGetTypeElement(Oidtype);
5050
staticOidGetInputFunction(Oidtype);
@@ -54,7 +54,7 @@ static void GetIndexRelations(Oid main_relation_oid,
5454
Relation**index_rels);
5555

5656
staticvoidCopyReadNewline(FILE*fp,int*newline);
57-
staticchar*CopyReadAttribute(FILE*fp,bool*isnull,char*delim,int*newline);
57+
staticchar*CopyReadAttribute(FILE*fp,bool*isnull,char*delim,int*newline,char*null_print);
5858

5959
staticvoidCopyAttributeOut(FILE*fp,char*string,char*delim);
6060
staticintCountTuples(Relationrelation);
@@ -219,7 +219,7 @@ CopyDonePeek(FILE *fp, int c, int pickup)
219219

220220
void
221221
DoCopy(char*relname,boolbinary,booloids,boolfrom,boolpipe,
222-
char*filename,char*delim,intfileumask)
222+
char*filename,char*delim,char*null_print,intfileumask)
223223
{
224224
/*----------------------------------------------------------------------------
225225
Either unload or reload contents of class <relname>, depending on <from>.
@@ -232,7 +232,8 @@ DoCopy(char *relname, bool binary, bool oids, bool from, bool pipe,
232232
Iff <binary>, unload or reload in the binary format, as opposed to the
233233
more wasteful but more robust and portable text format.
234234
235-
If in the text format, delimit columns with delimiter <delim>.
235+
If in the text format, delimit columns with delimiter <delim> and print
236+
NULL values as <null_print>.
236237
237238
<fileumask> is the umask(2) setting to use while creating an output file.
238239
This should usually be more liberal than the backend's normal 077 umask,
@@ -304,7 +305,7 @@ DoCopy(char *relname, bool binary, bool oids, bool from, bool pipe,
304305
"reading. Errno = %s (%d).",
305306
geteuid(),filename,strerror(errno),errno);
306307
}
307-
CopyFrom(rel,binary,oids,fp,delim);
308+
CopyFrom(rel,binary,oids,fp,delim,null_print);
308309
}
309310
else
310311
{/* copy from database to file */
@@ -336,7 +337,7 @@ DoCopy(char *relname, bool binary, bool oids, bool from, bool pipe,
336337
"writing. Errno = %s (%d).",
337338
geteuid(),filename,strerror(errno),errno);
338339
}
339-
CopyTo(rel,binary,oids,fp,delim);
340+
CopyTo(rel,binary,oids,fp,delim,null_print);
340341
}
341342
if (!pipe)
342343
{
@@ -362,7 +363,7 @@ DoCopy(char *relname, bool binary, bool oids, bool from, bool pipe,
362363

363364

364365
staticvoid
365-
CopyTo(Relationrel,boolbinary,booloids,FILE*fp,char*delim)
366+
CopyTo(Relationrel,boolbinary,booloids,FILE*fp,char*delim,char*null_print)
366367
{
367368
HeapTupletuple;
368369
HeapScanDescscandesc;
@@ -449,7 +450,7 @@ CopyTo(Relation rel, bool binary, bool oids, FILE *fp, char *delim)
449450
pfree(string);
450451
}
451452
else
452-
CopySendString("\\N",fp);/* null indicator */
453+
CopySendString(null_print,fp);/* null indicator */
453454

454455
if (i==attr_count-1)
455456
CopySendChar('\n',fp);
@@ -520,7 +521,7 @@ CopyTo(Relation rel, bool binary, bool oids, FILE *fp, char *delim)
520521
}
521522

522523
staticvoid
523-
CopyFrom(Relationrel,boolbinary,booloids,FILE*fp,char*delim)
524+
CopyFrom(Relationrel,boolbinary,booloids,FILE*fp,char*delim,char*null_print)
524525
{
525526
HeapTupletuple;
526527
AttrNumberattr_count;
@@ -711,7 +712,7 @@ CopyFrom(Relation rel, bool binary, bool oids, FILE *fp, char *delim)
711712
lineno++;
712713
if (oids)
713714
{
714-
string=CopyReadAttribute(fp,&isnull,delim,&newline);
715+
string=CopyReadAttribute(fp,&isnull,delim,&newline,null_print);
715716
if (string==NULL)
716717
done=1;
717718
else
@@ -724,7 +725,7 @@ CopyFrom(Relation rel, bool binary, bool oids, FILE *fp, char *delim)
724725
}
725726
for (i=0;i<attr_count&& !done;i++)
726727
{
727-
string=CopyReadAttribute(fp,&isnull,delim,&newline);
728+
string=CopyReadAttribute(fp,&isnull,delim,&newline,null_print);
728729
if (isnull)
729730
{
730731
values[i]=PointerGetDatum(NULL);
@@ -1122,10 +1123,11 @@ CopyReadNewline(FILE *fp, int *newline)
11221123
*
11231124
* delim is the string of acceptable delimiter characters(s).
11241125
* *newline remembers whether we've seen a newline ending this tuple.
1126+
* null_print says how NULL values are represented
11251127
*/
11261128

11271129
staticchar*
1128-
CopyReadAttribute(FILE*fp,bool*isnull,char*delim,int*newline)
1130+
CopyReadAttribute(FILE*fp,bool*isnull,char*delim,int*newline,char*null_print)
11291131
{
11301132
StringInfoDataattribute_buf;
11311133
charc;
@@ -1207,6 +1209,13 @@ CopyReadAttribute(FILE *fp, bool *isnull, char *delim, int *newline)
12071209
c=val&0377;
12081210
}
12091211
break;
1212+
/* This is a special hack to parse `\N' as <backslash-N>
1213+
rather then just 'N' to provide compatibility with
1214+
the default NULL output. -- pe */
1215+
case'N':
1216+
appendStringInfoChar(&attribute_buf,'\\');
1217+
c='N';
1218+
break;
12101219
case'b':
12111220
c='\b';
12121221
break;
@@ -1225,9 +1234,6 @@ CopyReadAttribute(FILE *fp, bool *isnull, char *delim, int *newline)
12251234
case'v':
12261235
c='\v';
12271236
break;
1228-
case'N':
1229-
*isnull= (bool) true;
1230-
break;
12311237
case'.':
12321238
c=CopyGetChar(fp);
12331239
if (c!='\n')
@@ -1266,6 +1272,9 @@ CopyReadAttribute(FILE *fp, bool *isnull, char *delim, int *newline)
12661272
returncvt;
12671273
}
12681274
#endif
1275+
if (strcmp(attribute_buf.data,null_print)==0)
1276+
*isnull= true;
1277+
12691278
returnattribute_buf.data;
12701279

12711280
endOfFile:

‎src/backend/commands/user.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 1994, Regents of the University of California
77
*
8-
* $Id: user.c,v 1.41 1999/12/12 05:57:28 momjian Exp $
8+
* $Id: user.c,v 1.42 1999/12/14 00:08:13 momjian Exp $
99
*
1010
*-------------------------------------------------------------------------
1111
*/
@@ -77,6 +77,7 @@ update_pg_pwd()
7777
false,/* pipe */
7878
tempname,/* filename */
7979
CRYPT_PWD_FILE_SEPSTR,/* delim */
80+
"",/* nulls */
8081
0077);/* fileumask */
8182
/*
8283
* And rename the temp file to its final name, deleting the old pg_pwd.

‎src/backend/parser/gram.y

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.121 1999/12/10 07:37:35 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.122 1999/12/14 00:08:15 momjian Exp $
1414
*
1515
* HISTORY
1616
* AUTHORDATEMAJOR EVENT
@@ -147,7 +147,7 @@ static Node *doNegate(Node *n);
147147

148148
%type <str>TriggerEvents, TriggerFuncArg
149149

150-
%type <str>relation_name, copy_file_name, copy_delimiter, def_name,
150+
%type <str>relation_name, copy_file_name, copy_delimiter,copy_null,def_name,
151151
database_name, access_method_clause, access_method, attr_name,
152152
class, index_name, name, func_name, file_name, aggr_argtype
153153

@@ -802,7 +802,7 @@ opt_id: ColId{ $$ = $1; }
802802
*
803803
*****************************************************************************/
804804

805-
CopyStmt: COPY opt_binary relation_name opt_with_copy copy_dirn copy_file_name copy_delimiter
805+
CopyStmt: COPY opt_binary relation_name opt_with_copy copy_dirn copy_file_name copy_delimiter copy_null
806806
{
807807
CopyStmt *n = makeNode(CopyStmt);
808808
n->binary = $2;
@@ -811,6 +811,7 @@ CopyStmt: COPY opt_binary relation_name opt_with_copy copy_dirn copy_file_name
811811
n->direction = $5;
812812
n->filename = $6;
813813
n->delimiter = $7;
814+
n->null_print = $8;
814815
$$ = (Node *)n;
815816
}
816817
;
@@ -850,6 +851,8 @@ opt_using:USING{ $$ = TRUE; }
850851
| /*EMPTY*/{ $$ = TRUE; }
851852
;
852853

854+
copy_null: WITH NULL_P AS Sconst { $$ = $4; }
855+
| /*EMPTY*/ { $$ = "\\N"; }
853856

854857
/*****************************************************************************
855858
*

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.73 1999/12/10 03:55:59 momjian Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.74 1999/12/14 00:08:17 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -269,6 +269,7 @@ ProcessUtility(Node *parsetree,
269269
*/
270270
stmt->filename,
271271
stmt->delimiter,
272+
stmt->null_print,
272273
/*
273274
* specify 022 umask while writing files with COPY.
274275
*/

‎src/include/commands/copy.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
*
77
* Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Id: copy.h,v 1.6 1999/11/21 04:16:17 tgl Exp $
9+
* $Id: copy.h,v 1.7 1999/12/14 00:08:19 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -15,6 +15,6 @@
1515

1616

1717
voidDoCopy(char*relname,boolbinary,booloids,boolfrom,boolpipe,
18-
char*filename,char*delim,intfileumask);
18+
char*filename,char*delim,char*null_print,intfileumask);
1919

2020
#endif/* COPY_H */

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
*
77
* Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Id: parsenodes.h,v 1.90 1999/12/10 07:37:32 tgl Exp $
9+
* $Id: parsenodes.h,v 1.91 1999/12/14 00:08:21 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -130,6 +130,7 @@ typedef struct CopyStmt
130130
intdirection;/* TO or FROM */
131131
char*filename;/* if NULL, use stdin/stdout */
132132
char*delimiter;/* delimiter character, \t by default */
133+
char*null_print;/* how to print NULLs, `\N' by default */
133134
}CopyStmt;
134135

135136
/* ----------------------

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp