1

I have a 30 GB tab separated text file with 11 columns. I want to import it to a single column PostgreSQL table. How can I do this?

What should I put for delimiter variable in \copy command? Every character could possibly exist in the text file. How can I force PostgreSQL to consider the file as a one column file?

below is the first 5 rows of the data, some rows are not English and can be in any language.

01B27BE8    Evaluating Polarity for Verbal Phraseological Units evaluating polarity for verbal phraseological units 2014    2014/11/16  10.1007/978-3-319-13647-9_19    mexican international conference on artificial intelligence micai       42D7146F    19517
027D0030    Automatic Monitoring the Content of Audio Broadcasted by Internet Radio Stations    automatic monitoring the content of audio broadcasted by internet radio stations    2012    2012/10/27  10.1007/978-3-642-37807-2_11    mexican international conference on artificial intelligence micai   42D7146F    19444
7CFE299E    Towards a set of Measures for Evaluating Software Agent Autonomytowards a set of measures for evaluating software agent autonomy    2009    2009/11 10.1109/MICAI.2009.15   mexican international conference on artificial intelligence micai       42D7146F    18870
59BEBE1C    Learning Probability Densities of Optimization Problems with Constraints and Uncertainty    learning probability densities of optimization problems with constraints and uncertainty    2008    2008/10/27  10.1007/978-3-540-88636-5_25    mexican international conference on artificial intelligence micai       42D7146F    19444
5873C011    Towards a Model for an Immune System    towards a model for an immune system    2002    2002/04/22  10.1007/3-540-46016-0_42    mexican international conference on artificial intelligence micai       42D7146F19177
Ramin Zahedi
  • 455
  • 2
  • 6
  • 20
  • First thing is, having 11 columns into single column in table is not a much good idea. And to give you a solution, try using \n as delimiter. – Viki888 Dec 29 '16 at 10:16
  • Change the file, merge the columns into one big CSV containing only one column. (Use bash http://stackoverflow.com/questions/19602181/bash-extract-one-column-of-a-csv-file) and after that use Copy. – PeterRing Dec 29 '16 at 10:19
  • @Viki888 I need to import the data to a single column staging table first then I will change it. about the '\n' delimiter, I think it would cause the whole file to be a one row file with 100 millions columns. (my text file has 100 million rows) – Ramin Zahedi Dec 29 '16 at 10:20
  • @RaminZahedi Can you provide some data of your file? – Viki888 Dec 29 '16 at 10:25
  • Are you sure you e.g. have a `\b` ("backspace") as part of the data? –  Dec 29 '16 at 10:25
  • @Viki888 I added first 5 rows – Ramin Zahedi Dec 29 '16 at 10:30
  • @a_horse_with_no_name see the examples I just added – Ramin Zahedi Dec 29 '16 at 10:34
  • @Viki888 have you tried '\n' delimiter before? – Ramin Zahedi Dec 29 '16 at 10:37
  • @RaminZahedi I have not tried but it should work in your scenario. – Viki888 Dec 29 '16 at 10:38
  • @Viki888 this is the definition of delimiter in PostgreSQL documentation: "Specifies the character that separates columns within each row (line) of the file." – Ramin Zahedi Dec 29 '16 at 10:45
  • @RaminZahedi I do know the definition for delimiter. New line character `\n` as delimiter should work in your scenario. Try using it. – Viki888 Dec 29 '16 at 10:50
  • @Viki888: using `\n` will result in: "*COPY delimiter cannot be newline or carriage return*" –  Dec 29 '16 at 11:11

1 Answers1

1

Using e.g. \x01 as the delimiter works for me:

\copy the_table from 'data.txt' with (delimiter E'\x01')