Re: [GENERAL] Quoted NULLs with COPY FROM (and pgAdmin export data
George Pavlov wrote: This is actually turning into a bit of a pgAdmin issue: pgAdmin lets you export data in a format that seems to be unimportable back into the same table. If in the Export data to file form you check all columns under Quoting you will get quotes around your numeric NULLs that you will be unable to import back using COPY without resorting to preprocessing of some sort (unless someone tells me how COPY can use quoted NULLs). Does sound like something for the pgadmin team. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Quoted NULLs with COPY FROM
George Pavlov wrote: Did you try NULL AS ''? yes i did. that is the default and does not change the outcome (same errors about trying to insert an empty string into a numeric field. Well there's your problem. Quotes tend to imply a text field. Assuming you don't want to write a short Perl script to pre-process the file the simplest way would be to import into a temporary table with text columns rather than numeric, then copy from that into the real table (with CASE or two queries). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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: [GENERAL] Quoted NULLs with COPY FROM (and pgAdmin export data options)
Quotes tend to imply a text field. I think you meant to say quotes imply a non-null text field. And, yes, I am quite aware of that. The point of the thread was to see if there is any way of avoiding/overriding that assumption. Assuming you don't want to write a short Perl script to pre-process the file the simplest way would be to import into a temporary table with text columns rather than numeric, then copy from that into the real table (with CASE or two queries). Yes, both are valid alternatives. The point was to see if I could skip those steps. This is actually turning into a bit of a pgAdmin issue: pgAdmin lets you export data in a format that seems to be unimportable back into the same table. If in the Export data to file form you check all columns under Quoting you will get quotes around your numeric NULLs that you will be unable to import back using COPY without resorting to preprocessing of some sort (unless someone tells me how COPY can use quoted NULLs). George ---(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
[GENERAL] Quoted NULLs with COPY FROM
I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. George ---(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: [GENERAL] Quoted NULLs with COPY FROM
George Pavlov wrote: I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. Did you try NULL AS ''? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: don't forget to increase your free space map settings
Re: [GENERAL] Quoted NULLs with COPY FROM
I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. Did you try NULL AS ''? yes i did. that is the default and does not change the outcome (same errors about trying to insert an empty string into a numeric field. ---(end of broadcast)--- TIP 6: explain analyze is your friend
