Re: [PATCHES] allow CSV quote in NULL
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > Please find attached a minor patch to remove the constraints that a > > user can't include the delimiter or quote characters in a 'NULL AS' > > string when importing CSV files. > > This can't really be sane can it? hm? It's a problem I've run into a number of times and I finally got fed up with it enough to create a patch for it. It solves my problem and I don't believe breaks or adversely affects anything else. It also has to be explicitly asked for by the user, and when the user asks for it, what's done is intuitively what would be expected. I don't see the logic in forbidding the user from being able to do this... My specific case involved an integer field which was quoted in the CSV file (which isn't generally a problem, it's only an issue if it ends up being a an empty string). I could easily see it being used for text fields as well though, if, for example, they want empty strings, even when quoted, to import as NULLs. Of course, the default remains that it'd be imported as an empty string, having it be a NULL has to be explicitly requested. Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] allow CSV quote in NULL
Stephen Frost <[EMAIL PROTECTED]> writes: > Please find attached a minor patch to remove the constraints that a > user can't include the delimiter or quote characters in a 'NULL AS' > string when importing CSV files. This can't really be sane can it? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] allow CSV quote in NULL
Greetings,
Please find attached a minor patch to remove the constraints that a
user can't include the delimiter or quote characters in a 'NULL AS'
string when importing CSV files.
This allows a user to explicitly request that NULL conversion happen
on fields which are quoted. As the quote character is being allowed
to be in the 'NULL AS' string now, there's no reason to exclude the
delimiter character from being seen in that string as well, though
unless quoted using the CSV quote character it won't ever be matched.
An example of the usage:
sfrost*=# \copy billing_data from ~/BillingSamplePricerFile.csv
with csv header quote as '"' null as '""'
This is no contrived example, it's an issue I ran into earlier today
when I got a file which had (for reasons unknown to me and not easily
changed upstream):
"1","V","WASHDCABC12","","120033"...
Both of the ending columns shown are integer fields, the "" here being
used to indicate a NULL value.
Without the patch, an ERROR occurs:
sfrost=> \copy billing_data from ~/BillingSamplePricerFile.csv
with csv header quote as '"'
ERROR: invalid input syntax for integer: ""
And there's no way to get it to import with COPY CSV mode. The
patch adds this ability without affecting existing usage or changing
the syntax. Even with the patch an ERROR occurs with the default
treatment of CSV files:
sfrost=# \copy billing_data from ~/BillingSamplePricerFile.csv
with csv header quote as '"'
ERROR: invalid input syntax for integer: ""
Which would be expected. If the file is modified to remove the ""s
for NULL columns, it imports just fine with the syntax above.
It'd be really nice to have this included.
Thanks!
Stephen
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.285
diff -c -r1.285 copy.c
*** src/backend/commands/copy.c 20 Jun 2007 02:02:49 - 1.285
--- src/backend/commands/copy.c 27 Jul 2007 04:13:15 -
***
*** 926,944
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force not null only available using COPY FROM")));
- /* Don't allow the delimiter to appear in the null string. */
- if (strchr(cstate->null_print, cstate->delim[0]) != NULL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("COPY delimiter must not appear in the NULL specification")));
-
- /* Don't allow the CSV quote char to appear in the null string. */
- if (cstate->csv_mode &&
- strchr(cstate->null_print, cstate->quote[0]) != NULL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("CSV quote character must not appear in the NULL specification")));
-
/* Disallow file COPY except to superusers. */
if (!pipe && !superuser())
ereport(ERROR,
--- 926,931
***
*** 2888,2894
{
bool found_delim = false;
bool in_quote = false;
- bool saw_quote = false;
char *start_ptr;
char *end_ptr;
int input_len;
--- 2875,2880
***
*** 2921,2927
/* start of quoted field (or part of field) */
if (c == quotec && !in_quote)
{
- saw_quote = true;
in_quote = true;
continue;
}
--- 2907,2912
***
*** 2971,2977
/* Check whether raw input matched null marker */
input_len = end_ptr - start_ptr;
! if (!saw_quote && input_len == cstate->null_print_len &&
strncmp(start_ptr, cstate->null_print, input_len) == 0)
fieldvals[fieldno] = NULL;
--- 2956,2962
/* Check whether raw input matched null marker */
input_len = end_ptr - start_ptr;
! if (input_len == cstate->null_print_len &&
strncmp(start_ptr, cstate->null_print, input_len) == 0)
fieldvals[fieldno] = NULL;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] patch win32.mak of libpq
Oops, An expressional mistake. As for PostgreSQL CVS HEAD(8.3beta). libpq is created by MinGW (gcc). As for psqlODBC CVS HEAD(08.02.0402) (linked SECUR32.dll) SECUR32.dll link is libpq.dll. SECUR32.dll does not link to psqlodbc35w.dll Regards, Hiroshi Saito ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] patch win32.mak of libpq
Hi. Some test situations. As for PostgreSQL CVS HEAD(8.3beta). libpq is created by MinGW (gcc). As for psqlODBC CVS HEAD(08.02.0402) (linked SECUR32.dll) 1. VC6(psqlodbc.dsp) The run test of compile and a part is through in VC6. 2. VC8(psqlodbc.vcproj) The run test of compile and a part is through in VC8. This is one progress report. It will still continue. However, the check was completed.! Regards, Hiroshi Saito ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] patch win32.mak of libpq
Hi Magnus and all. From: "Magnus Hagander" Hiroshi Saito wrote: Ok. So there are actually two ways to go about it: 1) Discontinue support for MSVC6 and require MSVC8 2) Change it so that MSVC6 can still build libpq, just not with SSPI support. This can be done by conditionally enabling ENABLE_SSPI, so it's not that hard. The question is, if we go with option 2, is it something that anybody actually will *use*? I desire 1 as formal. However, It contains VC7.1 and VC8. Moreover, libpq.dll can be used by the module of VC6. Is there any actual reason to keep VC7.1 support? It is still used and has sufficient function. Then, Inoue-san is developing in the environment.:-) The project file of VC7.1 differs from VC8 a little. However, nmake.exe absorbs it. for the reasons, we are maintaining win32.mak. but, project file offers the minimum function in simple. MSDTC is a reason for being somewhat more complicated than standard compile. VC6 has one reason only to exist, and that's that it doesn't carry the dependencies that VC8 does. But VC7 does (different ones, but just as many). So if we're dropping support for VC6, my suggestion is that we drop support for *any* win32 compiler other than VC8 and MingW gcc. By the PostgreSQL main part, I think that you may limit with support of VC7.1, VC8, and MinGW (gcc) by SSPI to which you are working. Although our psqlODBC uses libpq.dll, a wired protocol realizes it. Although I have not tested SSPI yet, However, I am checking normal of compile. If I'm not mistaken, one of the original reasons we kept the win32.mak method around after we had the "complete msvc build" was for the ODBC folks. Are you saying that the ODBC guys are now happy with a MSVC8 build? Yes, They are offered as comfortable environment. pgAdmin and that's right.!:-) However, psqlODBC also contains legacy VC6. They will clear a problem by the cause libpq.dll wire. Even if it may be adjusted from now on. probably. I'm sorry, I don't fully understand you here. Which one of the following three is it (or is it something else altogether)? 1) Does ODBC *require* a MSVC6 build libpq.dll? 2) Can ODBC work with MVC8 built libpq, but ODBC is built with MSVC6? 3) Can ODBC be built with MSVC8 and use the MSVC8 built libpq? It would be unfortunate if ODBC has to ship with a different set of dependencies than libpq, but as long as they build with either VC6 or VC8 that shouldn't happen. 1 above would be really bad, but I'm 99% sure that's not so, since I've actually tested SSPI auth with such a libpq. IMO, the best option would be 3, but I don't know enough about the ODBC driver to comment on there. I'll CC this to the odbc list so we can get more input from other people there. I'm sure that 3 is sufficient. I will begin the preparation. Of course, if there is a problem, though it will be corrected. P.S) The present situation.(as for psqlODBC) Compile (VC6, VC8) does not look at a problem by CVS-HEAD of pgsql.!! But, The test is not completedI apologize for the overdue work... Regards, Hiroshi Saito ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] patch win32.mak of libpq
Hiroshi Saito wrote: >> Ok. So there are actually two ways to go about it: >> 1) Discontinue support for MSVC6 and require MSVC8 >> >> 2) Change it so that MSVC6 can still build libpq, just not with SSPI >> support. This can be done by conditionally enabling ENABLE_SSPI, so it's >> not that hard. >> >> The question is, if we go with option 2, is it something that anybody >> actually will *use*? > > I desire 1 as formal. However, It contains VC7.1 and VC8. > Moreover, libpq.dll can be used by the module of VC6. Is there any actual reason to keep VC7.1 support? VC6 has one reason only to exist, and that's that it doesn't carry the dependencies that VC8 does. But VC7 does (different ones, but just as many). So if we're dropping support for VC6, my suggestion is that we drop support for *any* win32 compiler other than VC8 and MingW gcc. >> If I'm not mistaken, one of the original reasons we kept the win32.mak >> method around after we had the "complete msvc build" was for the ODBC >> folks. Are you saying that the ODBC guys are now happy with a MSVC8 >> build? > > Yes, They are offered as comfortable environment. pgAdmin and that's > right.!:-) > However, psqlODBC also contains legacy VC6. They will clear a problem by > the cause libpq.dll wire. Even if it may be adjusted from now on. probably. I'm sorry, I don't fully understand you here. Which one of the following three is it (or is it something else altogether)? 1) Does ODBC *require* a MSVC6 build libpq.dll? 2) Can ODBC work with MVC8 built libpq, but ODBC is built with MSVC6? 3) Can ODBC be built with MSVC8 and use the MSVC8 built libpq? It would be unfortunate if ODBC has to ship with a different set of dependencies than libpq, but as long as they build with either VC6 or VC8 that shouldn't happen. 1 above would be really bad, but I'm 99% sure that's not so, since I've actually tested SSPI auth with such a libpq. IMO, the best option would be 3, but I don't know enough about the ODBC driver to comment on there. I'll CC this to the odbc list so we can get more input from other people there. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] patch win32.mak of libpq
Hi. From: "Magnus Hagander" On Wed, Jul 25, 2007 at 10:30:29PM +0900, Hiroshi Saito wrote: >On Wed, Jul 25, 2007 at 02:36:41PM +0900, Hiroshi Saito wrote: >>Hi Magnus, and Dave. >> >>Sorry, I'm a somewhat late reaction... This is the present correspondence. >>However, VC6 does not support SSAPI. Therefore, the next release makes >>VC6 the outside of an object. > >I think VC6 should support it Ok. You probably just need to upgrade your >Platform SDK to a version that's for Windows 2000 or newer. I think VC6 >shipws with the NT4 SDK by default. It looks like it' just missing >secur32.lib. Ahh yes. However, MS says.! The last SDK that will work with VC 6.0 is the February 2003 Edition. http://www.microsoft.com/msdownload/platformsdk/sdkupdate/ this SDK is newer and does not support VC6.:-( Ooh. Wasn't aware of that :-( Now I see what you mean. Yea, It is that there was nothing in public policy Ok. So there are actually two ways to go about it: 1) Discontinue support for MSVC6 and require MSVC8 2) Change it so that MSVC6 can still build libpq, just not with SSPI support. This can be done by conditionally enabling ENABLE_SSPI, so it's not that hard. The question is, if we go with option 2, is it something that anybody actually will *use*? I desire 1 as formal. However, It contains VC7.1 and VC8. Moreover, libpq.dll can be used by the module of VC6. If I'm not mistaken, one of the original reasons we kept the win32.mak method around after we had the "complete msvc build" was for the ODBC folks. Are you saying that the ODBC guys are now happy with a MSVC8 build? Yes, They are offered as comfortable environment. pgAdmin and that's right.!:-) However, psqlODBC also contains legacy VC6. They will clear a problem by the cause libpq.dll wire. Even if it may be adjusted from now on. probably. Thanks!! Regards, Hiroshi Saito ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] patch win32.mak of libpq
On Wed, Jul 25, 2007 at 10:30:29PM +0900, Hiroshi Saito wrote: > >On Wed, Jul 25, 2007 at 02:36:41PM +0900, Hiroshi Saito wrote: > >>Hi Magnus, and Dave. > >> > >>Sorry, I'm a somewhat late reaction... This is the present correspondence. > >>However, VC6 does not support SSAPI. Therefore, the next release makes > >>VC6 the outside of an object. > > > >I think VC6 should support it Ok. You probably just need to upgrade your > >Platform SDK to a version that's for Windows 2000 or newer. I think VC6 > >shipws with the NT4 SDK by default. It looks like it' just missing > >secur32.lib. > > Ahh yes. However, MS says.! > The last SDK that will work with VC 6.0 is the February 2003 Edition. > http://www.microsoft.com/msdownload/platformsdk/sdkupdate/ > this SDK is newer and does not support VC6.:-( Ooh. Wasn't aware of that :-( Now I see what you mean. Ok. So there are actually two ways to go about it: 1) Discontinue support for MSVC6 and require MSVC8 2) Change it so that MSVC6 can still build libpq, just not with SSPI support. This can be done by conditionally enabling ENABLE_SSPI, so it's not that hard. The question is, if we go with option 2, is it something that anybody actually will *use*? If I'm not mistaken, one of the original reasons we kept the win32.mak method around after we had the "complete msvc build" was for the ODBC folks. Are you saying that the ODBC guys are now happy with a MSVC8 build? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
