Обсуждение: How to remove quotes from COPY TO result?
PostgreSQL 8.4.1 I have a table "abbreviation" created as follows: id serial NOT NULL, full_name character varying(16), short_name character varying(16), I import some data from a tab delimited text file like so: 1 "STREET" "ST" 2 "ROAD" "RD" using COPY abbreviation FROM <<myfile>> The data is imported but I want the character columns to be stripped of the enclosing double-quotes and they are not. Whatdo I have to do? TIA Michael Rowan mike.rowan@internode.on.net 11 Kingscote Street ALBERTON South Australia 5014 tel 618 8240 3993
On 07/17/2010 09:45 PM, Michael Rowan wrote: > PostgreSQL 8.4.1 > > I have a table "abbreviation" created as follows: > > id serial NOT NULL, full_name character varying(16), short_name > character varying(16), > > I import some data from a tab delimited text file like so: > > 1 "STREET" "ST" 2 "ROAD" "RD" > > using COPY abbreviation FROM <<myfile>> > > The data is imported but I want the character columns to be stripped > of the enclosing double-quotes and they are not. What do I have to > do? See: http://www.postgresql.org/docs/8.4/interactive/sql-copy.html COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; select * from abbreviation; id | full_name | short_name ----+-----------+------------ 1 | STREET | ST 2 | ROAD | RD (2 rows) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Вложения
Thanks Joe. That works fine, but gave me a hint to use the escape string syntax for escapes e.g., E'\r\n'. I will attempt to discover what that means ;-) Thanks again Mike On 18/07/2010, at 4:14 PM, Joe Conway wrote: > On 07/17/2010 09:45 PM, Michael Rowan wrote: >> PostgreSQL 8.4.1 >> >> I have a table "abbreviation" created as follows: >> >> id serial NOT NULL, full_name character varying(16), short_name >> character varying(16), >> >> I import some data from a tab delimited text file like so: >> >> 1 "STREET" "ST" 2 "ROAD" "RD" >> >> using COPY abbreviation FROM <<myfile>> >> >> The data is imported but I want the character columns to be stripped >> of the enclosing double-quotes and they are not. What do I have to >> do? > > See: > http://www.postgresql.org/docs/8.4/interactive/sql-copy.html > > COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; > select * from abbreviation; > id | full_name | short_name > ----+-----------+------------ > 1 | STREET | ST > 2 | ROAD | RD > (2 rows) > > HTH, > > Joe > > -- > Joe Conway > credativ LLC: http://www.credativ.us > Linux, PostgreSQL, and general Open Source > Training, Service, Consulting, & 24x7 Support > Michael Rowan mike.rowan@internode.on.net 11 Kingscote Street ALBERTON South Australia 5014 tel 618 8240 3993
On 07/18/2010 02:00 AM, Michael Rowan wrote: > Thanks Joe. That works fine, but gave me a hint to use the escape string syntax for escapes e.g., E'\r\n'. > > I will attempt to discover what that means ;-) Sorry, probably should have mentioned that but it was late when I replied... See: http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html http://www.postgresql.org/docs/8.4/interactive/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS In particular: If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, where backslash escapes were always recognized. Although standard_conforming_strings currently defaults to off, the default will change to on in a future release for improved standards compliance. Applications are therefore encouraged to migrate away from using backslash escapes. If you need to use a backslash escape to represent a special character, write the string constant with an E to be sure it will be handled the same way in future releases. In addition to standard_conforming_strings, the configuration parameters escape_string_warning and backslash_quote govern treatment of backslashes in string constants. So I would have been better to write it as: COPY abbreviation FROM 'filename' WITH DELIMITER E'\t' CSV; but old habits die hard ;-) Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Вложения
Joe Conway wrote: -- Start of PGP signed section. > On 07/17/2010 09:45 PM, Michael Rowan wrote: > > PostgreSQL 8.4.1 > > > > I have a table "abbreviation" created as follows: > > > > id serial NOT NULL, full_name character varying(16), short_name > > character varying(16), > > > > I import some data from a tab delimited text file like so: > > > > 1 "STREET" "ST" > > 2 "ROAD" "RD" > > > > using COPY abbreviation FROM <<myfile>> > > > > The data is imported but I want the character columns to be stripped > > of the enclosing double-quotes and they are not. What do I have to > > do? > > See: > http://www.postgresql.org/docs/8.4/interactive/sql-copy.html > > COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; > select * from abbreviation; > id | full_name | short_name > ----+-----------+------------ > 1 | STREET | ST > 2 | ROAD | RD > (2 rows) How were the double-quotes removed? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 10 August 2010 00:53, Bruce Momjian <bruce@momjian.us> wrote: > Joe Conway wrote: > -- Start of PGP signed section. >> On 07/17/2010 09:45 PM, Michael Rowan wrote: >> > PostgreSQL 8.4.1 >> > >> > I have a table "abbreviation" created as follows: >> > >> > id serial NOT NULL, full_name character varying(16), short_name >> > character varying(16), >> > >> > I import some data from a tab delimited text file like so: >> > >> > 1 "STREET" "ST" >> > 2 "ROAD" "RD" >> > >> > using COPY abbreviation FROM <<myfile>> >> > >> > The data is imported but I want the character columns to be stripped >> > of the enclosing double-quotes and they are not. What do I have to >> > do? >> >> See: >> http://www.postgresql.org/docs/8.4/interactive/sql-copy.html >> >> COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; >> select * from abbreviation; >> id | full_name | short_name >> ----+-----------+------------ >> 1 | STREET | ST >> 2 | ROAD | RD >> (2 rows) > > How were the double-quotes removed? Doesn't the "WITH ... CSV" handle that automatically? The documentation implies to me that quotes would be stripped, although it talks more about writing CSV files than reading them. I haven't tried it, though. -- Michael Wood <esiotrot@gmail.com>
On 8/10/10 2:58 AM, Michael Wood wrote: > On 10 August 2010 00:53, Bruce Momjian <bruce@momjian.us> wrote: >> Joe Conway wrote: >>> COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; >>> select * from abbreviation; >>> id | full_name | short_name >>> ----+-----------+------------ >>> 1 | STREET | ST >>> 2 | ROAD | RD >>> (2 rows) >> >> How were the double-quotes removed? > > Doesn't the "WITH ... CSV" handle that automatically? Yes > The documentation implies to me that quotes would be stripped, > although it talks more about writing CSV files than reading them. I > haven't tried it, though. It works -- I tried it myself before I posted ;-) Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Joseph Conway wrote: > On 8/10/10 2:58 AM, Michael Wood wrote: > > On 10 August 2010 00:53, Bruce Momjian <bruce@momjian.us> wrote: > >> Joe Conway wrote: > >>> COPY abbreviation FROM 'filename' WITH DELIMITER '\t' CSV; > >>> select * from abbreviation; > >>> id | full_name | short_name > >>> ----+-----------+------------ > >>> 1 | STREET | ST > >>> 2 | ROAD | RD > >>> (2 rows) > >> > >> How were the double-quotes removed? > > > > Doesn't the "WITH ... CSV" handle that automatically? > > Yes > > > The documentation implies to me that quotes would be stripped, > > although it talks more about writing CSV files than reading them. I > > haven't tried it, though. > > It works -- I tried it myself before I posted ;-) Oh, I see the CSV now. I was confused. Sorry. I was not aware you could change the delimiter in CSV. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +