0

I have a file with some data which I need to insert in a table. I am using gpfdist-external table - table to load the file.

I am able to load data in table, but my problem is the fields in input file are enclosed with double quotes like "Emp Name" and the same is going to the database. I don't want these double quotes to go in database. Only the value inside those quotes should go.

I found this somewhere [ENCLOSED BY '"'] but it is not working in greenplum. Kindly tell me where i can change in my external table so that only Value inside the double quotes should go in database not the double quotes.

Thanks and Regards, Sunny

user2148036
  • 11
  • 1
  • 5

1 Answers1

1

ENCLOSED BY is MySQL syntax. For PostgreSQL, use COPY operator:

COPY mytable FROM 'filename' CSV HEADER

If you want to specify quoting style, add QUOTE 'quote' - but " is already the default.

One more note: you should upgrade to PostgreSQL 9.2 (or at least 9.1). PostgreSQL 8.4 is very old and not supported very well.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • Thanks MVP, I tried this but i am facing the same issue in Copy also. My File is | delimited and in linux server. Quote as '"' is for only csv delimited files. Here is what i have used COPY public.Data_test from '/sxm240/JunkYard/mdrtest.txt' with delimiter as '|' HEADER NULL as ''; And i m getting data in my table enclosed with double quotes like "NEW ORLEANS", whereas what i want is the value should be like NEW ORLEANS only. – user2148036 Mar 08 '13 at 11:43
  • Hi, I am sorry i didn't check properly. But my issue got resolved. Thanks a lot for your help. I have added CSV quote as '"'; as last and it worked... Thank You, Sunny – user2148036 Mar 08 '13 at 12:05
  • Just i have 1 query.. whats the difference between COPY command and the [gpfdist-external table - table] to load the file? and which is better? Thanks again – user2148036 Mar 08 '13 at 12:06
  • I never heard of `gpfdist-external` command before - it must be something developed locally or by Greenplum – mvp Mar 08 '13 at 17:06