Re: [PATCHES] COPY for CSV documentation
Tom Lane said: COPY a FROM stdin WITH DELIMITER , QUOTE '' EQUOTE ''; EQUOTE for embedded quote. ESCAPE would be better no? It's already a keyword ... much better. BTW, don't forget that the syntax options have to be provided in psql's \copy as well. Did the patch cover that? No, because it didn't make a syntax change. I admit I didn't test that though. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY for CSV documentation
Bruce Momjian said:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]
DELIMITER default to tab, except in CSV mode, where it is a comma.
That sounds very clear to me.
TIMTOWTDI, as we say in the perl world, and we could debate it endlessly.
This looks fine to me.
a few points:
. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE and
ESCAPE as well as for DELIMITER.
cheers
andrew
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] COPY for CSV documentation
Andrew Dunstan wrote:
Bruce Momjian said:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]
DELIMITER default to tab, except in CSV mode, where it is a comma.
That sounds very clear to me.
TIMTOWTDI, as we say in the perl world, and we could debate it endlessly.
This looks fine to me.
a few points:
. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE and
ESCAPE as well as for DELIMITER.
Yes, agreed.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] COPY for CSV documentation
Bruce Momjian said:
Andrew Dunstan wrote:
Bruce Momjian said:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]
DELIMITER default to tab, except in CSV mode, where it is a comma.
That sounds very clear to me.
TIMTOWTDI, as we say in the perl world, and we could debate it
endlessly. This looks fine to me.
a few points:
. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE
and ESCAPE as well as for DELIMITER.
Andrew, would you like me to take your patch, implement the changes
discussed, add \copy, and post the result for your testing?
That would be great. Thanks. I don't think the changes to what I did with
copy.c will be huge.
BTW, how to do this is asked at least once a day on the #postgresql
channel on freenode - a lot of people *really* want this.
cheers
andrew
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [PATCHES] COPY for CSV documentation
On Mon, Apr 12, 2004 at 02:26:14 -0400, Andrew Dunstan [EMAIL PROTECTED] wrote: a few points: . in CSV mode, NULL should default to '' - that was in what I sent in. Postgres normally treats an empty string as an empty string. Are you sure you really want it to be treated as a NULL by default in this one place? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] pg_restore ignore error patch
Dear Andrew, I transfer some data from one server to the other. On such occasion, I use my laptop to connect with server X, I download the data, then I restore them to server Y. The versions on my laptop and both servers are likely to be different. My laptop is likely to have some development version, and the servers may be in 7.3 or 7.4. What problems have you encountered other than dollar quoting? If you're interested to know, there were some complaints about the new default_with_oids setting, as far as I can remember. In the most general case, ISTM you would need to teach pg_dump how to degrade gracefully, and a m:1 sources to targets relationship suddenly becomes m:m. I'm not convinced it is worth the trouble. I don't know what is worth the trouble. I'm just reporting a feature that make the tool not work properly for me. I've fixed part of the problem and submitted a patch, and I just noted in the mail that there was another small problem that I did not fix. I agree that fixing this would mean redesigning the tool in depth. That's why I did not fixed it in the patch I submitted. My opinion would be that pg_dump should just dump the data in some raw format, and that all syntax re-generation issues should be dealt with by pg_restore (INSERT vs COPY, quoting, and so on). But the tool was not resigned this way in the beginning. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY for CSV documentation
Bruno Wolff III [EMAIL PROTECTED] writes: On Mon, Apr 12, 2004 at 02:26:14 -0400, Andrew Dunstan [EMAIL PROTECTED] wrote: a few points: . in CSV mode, NULL should default to '' - that was in what I sent in. Postgres normally treats an empty string as an empty string. Are you sure you really want it to be treated as a NULL by default in this one place? I think that's a spectacularly bad idea too. People who really want that can write NULL '', but it shouldn't be implied by CSV mode. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] COPY for CSV documentation
Andrew Dunstan wrote: Tom Lane wrote: Bruno Wolff III [EMAIL PROTECTED] writes: On Mon, Apr 12, 2004 at 02:26:14 -0400, Andrew Dunstan [EMAIL PROTECTED] wrote: a few points: . in CSV mode, NULL should default to '' - that was in what I sent in. Postgres normally treats an empty string as an empty string. Are you sure you really want it to be treated as a NULL by default in this one place? I think that's a spectacularly bad idea too. People who really want that can write NULL '', but it shouldn't be implied by CSV mode. Spectacularly? Hmm. My approach was that the default should be the most common case. Perhaps on import it's a tossup, but on export a CSV containing lots of \N cells is likely to be ... unexpected. But, honestly, it's not worth dying in a ditch over. It is my understanding that \N is a valid column value (no backslash escape in CSV, right?), so we can't use it for NULL. The only thing I can think of is for NULL to be: ,, (no quotes) and a zero-length string to be: ,, How do most applications handle those two cases? If they accept either, can we use that so we can read our own CSV files without losing the NULL specification? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] COPY for CSV documentation
On Mon, Apr 12, 2004 at 10:30:22 -0400, Bruce Momjian [EMAIL PROTECTED] wrote: It is my understanding that \N is a valid column value (no backslash escape in CSV, right?), so we can't use it for NULL. The only thing I can think of is for NULL to be: ,, (no quotes) and a zero-length string to be: ,, How do most applications handle those two cases? If they accept either, can we use that so we can read our own CSV files without losing the NULL specification? I think the above are going to be treated as equvialent by most CSV parsers. There doesn't seem to be a standard for CSV. From what I found describing it, there isn't any feature for distinguishing NULLs from empty strings. So whatever gets done is going to be application specific. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] COPY for CSV documentation
Bruno Wolff III wrote: On Mon, Apr 12, 2004 at 10:30:22 -0400, Bruce Momjian [EMAIL PROTECTED] wrote: It is my understanding that \N is a valid column value (no backslash escape in CSV, right?), so we can't use it for NULL. The only thing I can think of is for NULL to be: ,, (no quotes) and a zero-length string to be: ,, How do most applications handle those two cases? If they accept either, can we use that so we can read our own CSV files without losing the NULL specification? I think the above are going to be treated as equvialent by most CSV parsers. There doesn't seem to be a standard for CSV. From what I found describing it, there isn't any feature for distinguishing NULLs from empty strings. So whatever gets done is going to be application specific. I am thinking we could enable this NULL handling by default, and allow it to be over-ridden with the NULL keyword. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] COPY for CSV documentation
Bruce Momjian wrote: It is my understanding that \N is a valid column value (no backslash escape in CSV, right?), so we can't use it for NULL. \ is not conventionally magical in CSVs in the general case. That doesn't mean we couldn't use \N, but to me it would violate the principle of least surprise - no other application will interpret it in any special way, and the whole point about this facility is exchanging data with other applications. The only thing I can think of is for NULL to be: ,, (no quotes) and a zero-length string to be: ,, How do most applications handle those two cases? If they accept either, can we use that so we can read our own CSV files without losing the NULL specification? In fact, in the patch I sent in, no quoted string is marked as null when being read (so even if you use \N as the null marker, \N will be that literal and not a null marker). And the null marker, whatever it is, should be made quote safe by us throwing an error if it contains the quote marker, just as we now make sure that the null marker is delimiter-safe. I will check on the write behaviour - it might need ammending too. I'll submit a revised patch based on the original syntax scheme, and then you (Bruce) can make the syntax/psql changes that seem to be agreed on now - is that ok? The default NULL value issue can be determined at the end of any exhaustive debate we have - in the end it's a one line code change ;-) cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] aclitem accessor functions
Dear patchers,
Please find a attached a small patch that adds accessor functions
for aclitem so that it is not an opaque datatype.
I needed these functions to browse aclitems from user land. I can load
them when necessary, but it seems to me that these accessors for a backend
type belong to the backend, so I submit them.
I wasn't sure of what oid should be given...
I attributed new numbers at the end of pg_proc.h.
It validates for me against current cvs head.
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]*** ./src/backend/utils/adt/acl.c.orig Sat Nov 29 20:51:57 2003
--- ./src/backend/utils/adt/acl.c Mon Apr 12 15:23:17 2004
***
*** 874,879
--- 874,916
PG_RETURN_ACLITEM_P(aclitem);
}
+ /* give access to internal data within aclitem
+ */
+ Datum
+ aclitem_grantee(PG_FUNCTION_ARGS)
+ {
+ AclItem * a = PG_GETARG_ACLITEM_P(0);
+ PG_RETURN_INT32(a-ai_grantee);
+ }
+
+ Datum
+ aclitem_grantor(PG_FUNCTION_ARGS)
+ {
+ AclItem * a = PG_GETARG_ACLITEM_P(0);
+ PG_RETURN_INT32(a-ai_grantor);
+ }
+
+ Datum
+ aclitem_idtype(PG_FUNCTION_ARGS)
+ {
+ AclItem * a = PG_GETARG_ACLITEM_P(0);
+ PG_RETURN_INT32(ACLITEM_GET_IDTYPE(*a));
+ }
+
+ Datum
+ aclitem_privs(PG_FUNCTION_ARGS)
+ {
+ AclItem * a = PG_GETARG_ACLITEM_P(0);
+ PG_RETURN_INT32(ACLITEM_GET_PRIVS(*a));
+ }
+
+ Datum
+ aclitem_goptions(PG_FUNCTION_ARGS)
+ {
+ AclItem * a = PG_GETARG_ACLITEM_P(0);
+ PG_RETURN_INT32(ACLITEM_GET_GOPTIONS(*a));
+ }
+
static AclMode
convert_priv_string(text *priv_type_text)
{
*** ./src/include/catalog/pg_proc.h.origMon Apr 5 12:06:43 2004
--- ./src/include/catalog/pg_proc.h Mon Apr 12 16:54:52 2004
***
*** 3526,3531
--- 3526,3543
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 f f t t v 2 20 20 20
_null_ generate_series_int8 - _null_ ));
DESCR(non-persistent series generator);
+ /* aclitem utils */
+ DATA(insert OID = 2510 ( aclitem_grantorPGNSP PGUID 12 f f
t f i 1 23 1033 _null_ aclitem_grantor - _null_ ));
+ DESCR(extract user id grantor from aclitem);
+ DATA(insert OID = 2511 ( aclitem_granteePGNSP PGUID 12 f f
t f i 1 23 1033 _null_ aclitem_grantee - _null_ ));
+ DESCR(extract grantee (user or group id) from aclitem);
+ DATA(insert OID = 2512 ( aclitem_idtype PGNSP PGUID 12 f f
t f i 1 23 1033 _null_ aclitem_idtype - _null_ ));
+ DESCR(extract id type of grantee (0 public, 1 user, 2 group) from aclitem);
+ DATA(insert OID = 2513 ( aclitem_privs PGNSP PGUID 12 f f
t f i 1 23 1033 _null_ aclitem_privs - _null_ ));
+ DESCR(extract privileges from aclitem);
+ DATA(insert OID = 2514 ( aclitem_goptions PGNSP PGUID 12 f f
t f i 1 23 1033 _null_ aclitem_goptions - _null_ ));
+ DESCR(extract grant options from aclitem);
+
/*
* Symbolic values for provolatile column: these indicate whether the result
*** ./src/test/regress/expected/privileges.out.orig Mon Sep 15 02:26:31 2003
--- ./src/test/regress/expected/privileges.out Mon Apr 12 16:51:00 2004
***
*** 581,586
--- 581,600
t
(1 row)
+ -- aclitem utils small test
+ SELECT u1.usename AS u1, u2.usename AS u2,
+ aclitem_idtype(c.relacl[0]) AS idtype,
+ aclitem_privs(c.relacl[0]) AS privs,
+ aclitem_goptions(c.relacl[0]) AS goptions
+ FROM pg_class AS c, pg_user AS u1, pg_user AS u2
+ WHERE u1.usesysid = aclitem_grantor(c.relacl[0])
+ AND u2.usesysid = aclitem_grantee(c.relacl[0])
+ AND c.relname LIKE 'atest4';
+ u1 | u2 | idtype | privs | goptions
+ --+--++---+--
+ regressuser1 | regressuser1 | 1 | 127 | 127
+ (1 row)
+
-- clean up
\c regression
DROP FUNCTION testfunc2(int);
*** ./src/test/regress/sql/privileges.sql.orig Wed May 14 05:26:03 2003
--- ./src/test/regress/sql/privileges.sql Mon Apr 12 16:49:48 2004
***
*** 316,321
--- 316,330
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); --
true
+ -- aclitem utils small test
+ SELECT u1.usename AS u1, u2.usename AS u2,
+ aclitem_idtype(c.relacl[0]) AS idtype,
+ aclitem_privs(c.relacl[0]) AS privs,
+ aclitem_goptions(c.relacl[0]) AS goptions
+ FROM pg_class AS c, pg_user AS u1, pg_user AS u2
+ WHERE u1.usesysid = aclitem_grantor(c.relacl[0])
+ AND u2.usesysid = aclitem_grantee(c.relacl[0])
+ AND c.relname LIKE 'atest4';
-- clean up
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [PATCHES] COPY for CSV documentation
Andrew Dunstan wrote: In fact, in the patch I sent in, no quoted string is marked as null when being read (so even if you use \N as the null marker, \N will be that literal and not a null marker). And the null marker, whatever it is, should be made quote safe by us throwing an error if it contains the quote marker, just as we now make sure that the null marker is delimiter-safe. What value does an int column get if the input file has ',,'. Don't tell me zero? :-) Error? I will check on the write behaviour - it might need ammending too. I'll submit a revised patch based on the original syntax scheme, and then you (Bruce) can make the syntax/psql changes that seem to be agreed on now - is that ok? OK, go as far as you want and post it. I will turn around a new patch in a few hours after you post. The default NULL value issue can be determined at the end of any exhaustive debate we have - in the end it's a one line code change ;-) Agreed. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] change for stdin/stdout for \copy
I have applied the following patch:
---
Change psql \copy stdin/stdout to read from command input/output.
Add pstdin/pstdout to read from psql's stdin/stdout.
BACKWARD INCOMPATIBLE CHANGE
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.109
diff -c -c -r1.109 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 30 Mar 2004 15:54:33 - 1.109
--- doc/src/sgml/ref/psql-ref.sgml 10 Apr 2004 15:18:58 -
***
*** 706,712
termliteral\copy replaceable class=parametertable/replaceable
[ ( replaceable class=parametercolumn_list/replaceable ) ]
{ literalfrom/literal | literalto/literal }
! { replaceable class=parameterfilename/replaceable | stdin | stdout | - }
[ literalwith/literal ]
[ literaloids/literal ]
[ literaldelimiter [as] /literal 'replaceable
class=parametercharacter/replaceable' ]
--- 706,712
termliteral\copy replaceable class=parametertable/replaceable
[ ( replaceable class=parametercolumn_list/replaceable ) ]
{ literalfrom/literal | literalto/literal }
! { replaceable class=parameterfilename/replaceable | stdin | stdout |
pstdin | pstdout }
[ literalwith/literal ]
[ literaloids/literal ]
[ literaldelimiter [as] /literal 'replaceable
class=parametercharacter/replaceable' ]
***
*** 736,753
/para
para
! For literal\copy replaceable
class=parametertable/replaceable from replaceable
! class=parameterfilename/replaceable/literal operations,
! applicationpsql/application adds the option of using a
! hyphen instead of replaceable
! class=parameterfilename/replaceable. This causes
! literal\copy/literal to read rows from the same source that
! issued the command, continuing until literal\./literal is
! read or the stream reaches acronymEOF/. This option is
! useful for populating tables in-line within a SQL script file.
! In contrast, literal\copy from stdin/ always reads from
! applicationpsql/application's standard input.
/para
tip
--- 736,752
/para
para
! literal\copy replaceable
class=parametertable/replaceable from replaceable
! class=parameterstdin | stdout/replaceable/literal
! reads/writes based on the command input and output respectively.
! All rows are read from the same source that issued the command,
! continuing until literal\./literal is read or the stream
! reaches acronymEOF/. Output is sent to the same place as
! command output. To read/write from
! applicationpsql/application's standard input or output, use
! literalpstdin/ or literalpstdout/. This option is useful
! for populating tables in-line within a SQL script file.
/para
tip
***
*** 759,778
/para
/tip
- note
- para
- Note the difference in interpretation of
- literalstdin/literal and literalstdout/literal between
- literal\copy/literal and commandCOPY/command.
- In literal\copy/literal these always
- refer to applicationpsql/application's input and output
- streams. In commandCOPY/command, literalstdin/literal comes
- from wherever the commandCOPY/command itself came from (for
- example, a script run with the option-f/option option), while
- literalstdout/literal refers to the query output stream (see
- command\o/command meta-command below).
- /para
- /note
/listitem
/varlistentry
--- 758,763
Index: src/bin/psql/copy.c
===
RCS file: /cvsroot/pgsql-server/src/bin/psql/copy.c,v
retrieving revision 1.42
diff -c -c -r1.42 copy.c
*** src/bin/psql/copy.c 29 Jan 2004 12:34:59 - 1.42
--- src/bin/psql/copy.c 10 Apr 2004 15:19:05 -
***
*** 62,68
char *table;
char *column_list;
char *file; /* NULL = stdin/stdout */
! boolin_dash;/* true = use src stream not true
stdin */
boolfrom;
boolbinary;
booloids;
--- 62,68
char *table;
char *column_list;
char *file; /* NULL =
Re: [PATCHES] New socket code for win32
Patch applied with later socket.c file used. Thanks. This is a major Win32 accomplishment. --- Magnus Hagander wrote: Here's an attempt at new socket and signal code for win32. It works on the principle of turning sockets into non-blocking, and then emulate blocking behaviour on top of that, while allowing signals to run. Signals are now implemented using an event instead of APCs, thus getting rid of the issue of APCs not being compatible with old style sockets functions. It also moves the win32 specific code away from pqsignal.h/c into port/win32, and also removes the thread style workaround of the APC issue previously in place. In order to make things work, a few things are also changed in pgstat.c: 1) There is now a separate pipe to the collector and the bufferer. This is required because the pipe will otherwise only be signalled in one of the processes when the postmaster goes down. The MS winsock code for select() must have some kind of workaround for this behaviour, but I have found no stable way of doing that. You really are not supposed to use the same socket from more than one process (unless you use WSADuplicateSocket(), in which case the docs specifically say that only one will be flagged). 2) The check for postmaster death is moved into a separate select() call after the main loop. The previous behaviour select():ed on the postmaster pipe, while later explicitly saying we do NOT check for postmaster exit inside the loop. The issue was that the code relies on the same select() call seeing both the postmaster pipe *and* the pgstat pipe go away. This does not always happen, and it appears that useing WSAEventSelect() makes it even more common that it does not. Since it's only called when the process exits, I don't think using a separate select() call will have any significant impact on how the stats collector works. Anyway. Here is a patch for review. The two files go in backend/port/win32/. Passes all regression tests expected (time and ordering ones still fails, pgstat works) //Magnus Content-Description: newsignal.patch [ Attachment, skipping... ] Content-Description: signal.c [ Attachment, skipping... ] Content-Description: socket.c [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] COPY for CSV documentation
Bruce Momjian wrote:
Andrew Dunstan wrote:
In fact, in the patch I sent in, no quoted string is marked as null when
being read (so even if you use \N as the null marker, \N will be that
literal and not a null marker). And the null marker, whatever it is,
should be made quote safe by us throwing an error if it contains the
quote marker, just as we now make sure that the null marker is
delimiter-safe.
What value does an int column get if the input file has ',,'. Don't
tell me zero? :-) Error?
If the null marker is not an empty string, it gets an error, of course -
if it is it gets a null:
[EMAIL PROTECTED] pginst]$ echo ',,' | bin/psql -c create temp table
foo (a int, b text, c text); copy foo from stdin delimiter ',\' null
'N';
ERROR: invalid input syntax for integer:
CONTEXT: COPY foo, line 1, column a:
[EMAIL PROTECTED] pginst]$ echo ',,' | bin/psql -c create temp table
foo (a int, b text, c text); copy foo from stdin delimiter ',\' ;
[EMAIL PROTECTED] pginst]$
I hope that is expected behaviour - it's what *I* expect, at least.
I will check on the write behaviour - it might need ammending too.
I'll submit a revised patch based on the original syntax scheme, and
then you (Bruce) can make the syntax/psql changes that seem to be agreed
on now - is that ok?
OK, go as far as you want and post it. I will turn around a new patch
in a few hours after you post.
The default NULL value issue can be determined at the end of any
exhaustive debate we have - in the end it's a one line code change ;-)
Agreed.
Attached patch has these additions to previously posted patch:
. quote character may not appear in NULL marker
. any non-null value that matches the NULL marker is forced to be quoted
when written.
cheers
andrew
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.219
diff -c -r1.219 copy.c
*** src/backend/commands/copy.c 6 Apr 2004 13:21:33 - 1.219
--- src/backend/commands/copy.c 12 Apr 2004 16:21:33 -
***
*** 70,76
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE
} CopyReadResult;
/*
--- 70,77
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE,
! UNTERMINATED_FIELD
} CopyReadResult;
/*
***
*** 136,144
--- 137,148
static bool CopyReadLine(void);
static char *CopyReadAttribute(const char *delim, const char *null_print,
CopyReadResult *result,
bool *isnull);
+ static char *CopyReadAttributeCSV(const char *delim, const char *null_print,
+ CopyReadResult *result,
bool *isnull);
static Datum CopyReadBinaryAttribute(int column_no, FmgrInfo *flinfo,
Oid typelem, bool *isnull);
static void CopyAttributeOut(char *string, char *delim);
+ static void CopyAttributeOutCSV(char *string, char *delim, bool force_quote);
static List *CopyGetAttnums(Relation rel, List *attnamelist);
static void limit_printout_length(StringInfo buf);
***
*** 682,687
--- 686,692
List *attnumlist;
boolbinary = false;
booloids = false;
+ boolcsv_mode = false;
char *delim = NULL;
char *null_print = NULL;
Relationrel;
***
*** 744,751
if (!delim)
delim = \t;
if (!null_print)
! null_print = \\N;
/*
* Open and lock the relation, using the appropriate lock type.
--- 749,759
if (!delim)
delim = \t;
+ if (strlen(delim) 1)
+ csv_mode = true;
+
if (!null_print)
! null_print = csv_mode ? : \\N;
/*
* Open and lock the relation, using the appropriate lock type.
***
*** 772,783
psql's \\copy command also works for
anyone.)));
/*
!* Presently, only single-character delimiter strings are supported.
*/
! if (strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(COPY delimiter must be a single character)));
/*
* Don't allow the delimiter to appear in the null string.
--- 780,806
psql's \\copy command also works for
anyone.)));
/*
!* Only single-character delimiter strings are supported,
!* except in CSV mode, where the string must be
!* delimiter-char quote-char [escape-char]
*/
! if (!csv_mode strlen(delim) != 1)
Re: [PATCHES] COPY for CSV documentation
FYI, this CVS is turning into quite a job, but doing it right takes this
kind of effort.
---
Andrew Dunstan wrote:
Bruce Momjian wrote:
Andrew Dunstan wrote:
In fact, in the patch I sent in, no quoted string is marked as null when
being read (so even if you use \N as the null marker, \N will be that
literal and not a null marker). And the null marker, whatever it is,
should be made quote safe by us throwing an error if it contains the
quote marker, just as we now make sure that the null marker is
delimiter-safe.
What value does an int column get if the input file has ',,'. Don't
tell me zero? :-) Error?
If the null marker is not an empty string, it gets an error, of course -
if it is it gets a null:
[EMAIL PROTECTED] pginst]$ echo ',,' | bin/psql -c create temp table
foo (a int, b text, c text); copy foo from stdin delimiter ',\' null
'N';
ERROR: invalid input syntax for integer:
CONTEXT: COPY foo, line 1, column a:
[EMAIL PROTECTED] pginst]$ echo ',,' | bin/psql -c create temp table
foo (a int, b text, c text); copy foo from stdin delimiter ',\' ;
[EMAIL PROTECTED] pginst]$
I hope that is expected behaviour - it's what *I* expect, at least.
I will check on the write behaviour - it might need ammending too.
I'll submit a revised patch based on the original syntax scheme, and
then you (Bruce) can make the syntax/psql changes that seem to be agreed
on now - is that ok?
OK, go as far as you want and post it. I will turn around a new patch
in a few hours after you post.
The default NULL value issue can be determined at the end of any
exhaustive debate we have - in the end it's a one line code change ;-)
Agreed.
Attached patch has these additions to previously posted patch:
. quote character may not appear in NULL marker
. any non-null value that matches the NULL marker is forced to be quoted
when written.
cheers
andrew
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.219
diff -c -r1.219 copy.c
*** src/backend/commands/copy.c 6 Apr 2004 13:21:33 - 1.219
--- src/backend/commands/copy.c 12 Apr 2004 16:21:33 -
***
*** 70,76
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE
} CopyReadResult;
/*
--- 70,77
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE,
! UNTERMINATED_FIELD
} CopyReadResult;
/*
***
*** 136,144
--- 137,148
static bool CopyReadLine(void);
static char *CopyReadAttribute(const char *delim, const char *null_print,
CopyReadResult *result,
bool *isnull);
+ static char *CopyReadAttributeCSV(const char *delim, const char *null_print,
+CopyReadResult *result,
bool *isnull);
static Datum CopyReadBinaryAttribute(int column_no, FmgrInfo *flinfo,
Oid typelem, bool *isnull);
static void CopyAttributeOut(char *string, char *delim);
+ static void CopyAttributeOutCSV(char *string, char *delim, bool force_quote);
static List *CopyGetAttnums(Relation rel, List *attnamelist);
static void limit_printout_length(StringInfo buf);
***
*** 682,687
--- 686,692
List *attnumlist;
boolbinary = false;
booloids = false;
+ boolcsv_mode = false;
char *delim = NULL;
char *null_print = NULL;
Relationrel;
***
*** 744,751
if (!delim)
delim = \t;
if (!null_print)
! null_print = \\N;
/*
* Open and lock the relation, using the appropriate lock type.
--- 749,759
if (!delim)
delim = \t;
+ if (strlen(delim) 1)
+ csv_mode = true;
+
if (!null_print)
! null_print = csv_mode ? : \\N;
/*
* Open and lock the relation, using the appropriate lock type.
***
*** 772,783
psql's \\copy command also works for
anyone.)));
/*
! * Presently, only single-character delimiter strings are supported.
*/
! if (strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(COPY delimiter must be a single character)));
/*
* Don't allow the delimiter to appear in the null string.
--- 780,806
psql's
Re: [PATCHES] aclitem accessor functions
Fabien COELHO wrote: Please find a attached a small patch that adds accessor functions for aclitem so that it is not an opaque datatype. I needed these functions to browse aclitems from user land. I can load them when necessary, but it seems to me that these accessors for a backend type belong to the backend, so I submit them. Can you explain what you want to do from the user level? We already have a bunch of functions for analyzing privileges. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Updated COPY CSV patch
Andrew Dunstan wrote:
If the null marker is not an empty string, it gets an error, of
course - if it is it gets a null:
[EMAIL PROTECTED] pginst]$ echo ',,' | bin/psql -c create temp
table foo (a int, b text, c text); copy foo from stdin delimiter
',\' null 'N'; ERROR: invalid input syntax for integer:
CONTEXT: COPY foo, line 1, column a: [EMAIL PROTECTED]
pginst]$ echo ',,' | bin/psql -c create temp table foo (a int,
b text, c text); copy foo from stdin delimiter ',\' ;
[EMAIL PROTECTED] pginst]$
I hope that is expected behaviour - it's what *I* expect, at
least.
Attached patch has these additions to previously posted patch:
. quote character may not appear in NULL marker
. any non-null value that matches the NULL marker is forced to be quoted
when written.
OK, here is a new version of the patch that includes the grammar
changes we agreed upon, SGML changes, and \copy support. I will not
make any more changes without contacting you so feel free to make
adjustments and repost.
I have two open issues. First, CSV should support WITH OIDS, no?
Second, I found a problem with NULLs. If I do:
.
test= create table test (x text, y text);
CREATE TABLE
test= insert into test values ('', NULL);
INSERT 17221 1
test=
then this:
test= copy test to '/tmp/b' with csv;
creates:
,
and this:
test= copy test to '/tmp/b' with csv NULL 'fred';
creates:
,fred
Is that logical? A non-null field went from to nothing.
I think it is caused by this code:
bool force_quote = (strcmp(string, null_print) == 0);
CopyAttributeOutCSV(string, delim, quote, escape,
force_quote);
The reason it happens is that when the null string is '', it matches a
zero-length string, so the value is quoted. When the null stirng isn't
blank, a zero-length string doesn't match the null string so it isn't
quoted.I think we need to add special logic for zero-length strings
so they are always quoted, even if there is a special null string. This
will make our dumps more consistent, I think, or maybe the current
behavior is OK. It just struck me as strange.
I did a dump/reload test with a null string and null, and it worked
fine.
Is there any data that can not be dumped/reloaded via CSV?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/copy.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/copy.sgml,v
retrieving revision 1.55
diff -c -c -r1.55 copy.sgml
*** doc/src/sgml/ref/copy.sgml 13 Dec 2003 23:59:07 - 1.55
--- doc/src/sgml/ref/copy.sgml 13 Apr 2004 04:18:22 -
***
*** 26,32
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'replaceable
class=parameterdelimiter/replaceable' ]
! [ NULL [ AS ] 'replaceable class=parameternull string/replaceable' ]
]
COPY replaceable class=parametertablename/replaceable [ ( replaceable
class=parametercolumn/replaceable [, ...] ) ]
TO { 'replaceable class=parameterfilename/replaceable' | STDOUT }
--- 26,34
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'replaceable
class=parameterdelimiter/replaceable' ]
! [ NULL [ AS ] 'replaceable class=parameternull string/replaceable' ]
! [ CSV [ QUOTE [ AS ] 'replaceable class=parameterquote/replaceable'
]
! [ ESCAPE [ AS ] 'replaceable
class=parameterescape/replaceable' ] ]
COPY replaceable class=parametertablename/replaceable [ ( replaceable
class=parametercolumn/replaceable [, ...] ) ]
TO { 'replaceable class=parameterfilename/replaceable' | STDOUT }
***
*** 34,40
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'replaceable
class=parameterdelimiter/replaceable' ]
! [ NULL [ AS ] 'replaceable class=parameternull string/replaceable' ]
]
/synopsis
/refsynopsisdiv
--- 36,44
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'replaceable
class=parameterdelimiter/replaceable' ]
! [ NULL [ AS ] 'replaceable class=parameternull string/replaceable' ]
! [ CSV [ QUOTE [ AS ] 'replaceable class=parameterquote/replaceable'
]
! [ ESCAPE [ AS ] 'replaceable
class=parameterescape/replaceable' ] ]
/synopsis
/refsynopsisdiv
***
*** 136,142
para
Specifies copying the OID for each row. (An error is raised if
literalOIDS/literal is specified for a table that does not
! have OIDs.)
/para
/listitem
/varlistentry
--- 140,146
para
Specifies copying the
