How to correctly include double quotes in an insert statement

From: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: How to correctly include double quotes in an insert statement
Date: 2019-03-27 16:13:46
Message-ID: CAAY=A78uEcxcdPjoaxVJkJQuzh6i1DGRrH5wdkU_y2JKXgD1wQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I have a tab-delimited text file from which I have to populate a table.
After reviewing the data in such a text file I noticed that there are
fields containing double quotes.
For example, I found several rows like this:

--------------------------------------------------------------------------------------------------------------------------------------------
CODE DESCRIPTION
CATEGORY BRAND
--------------------------------------------------------------------------------------------------------------------------------------------
ACEITERA-102 "ACEITERA GOTA VISIBLE 102mm 4"" X 3/4""" LUBRICANTES RICSA
ACEITERA-13 "ACEITERA GOTA VISIBLE 25mm 1"" X 1/4""" LUBRICANTES
RICSA
ACEITERA-32 "ACEITERA GOTA VISIBLE 32mm 1-1/4"" X 1/4""" LUBRICANTES
RICSA

As you can see, the DESCRIPTION field is surrounded by double quotes.
Also, measurement in inches are included using a pair of double quotes.
I am writing code to perform the export process to a table and I already
removed the double quotes that surround the DESCRIPTION and also one of the
double quotes that represent the inches.
So, when the export process runs, each line is converted to the following
format before performing the INSERT operation:

ACEITERA-102 ACEITERA GOTA VISIBLE 102mm 4" X 3/4" LUBRICANTES RICSA
ACEITERA-13 ACEITERA GOTA VISIBLE 25mm 1" X 1/4" LUBRICANTES RICSA
ACEITERA-32 ACEITERA GOTA VISIBLE 32mm 1-1/4" X 1/4" LUBRICANTES
RICSA

However, I can see that using double quotes still causes some issues later
when running our system.
I have also tried to escape double quotes using \" without success like
this:

ACEITERA-102 ACEITERA GOTA VISIBLE 102mm 4\" X 3/4\" LUBRICANTES RICSA
ACEITERA-13 ACEITERA GOTA VISIBLE 25mm 1\" X 1/4\" LUBRICANTES
RICSA
ACEITERA-32 ACEITERA GOTA VISIBLE 32mm 1-1/4\" X 1/4\" LUBRICANTES
RICSA

My C# code reads each row of the text file and splits it to get each field
separately.
The result of the split function is a string array of 4 items, for example:

a[0] contains the CODE
a[1] contains the DESCRIPTION
a[2] contains the CATEGORY
a[3] contains the BRAND

I have read several proposed solutions by googling the subject but I have
not been able to make it work correctly.
I will very much appreciate your feedback.

Respectfully,
Jorge Maldonado

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Frank Alberto Rodriguez 2019-03-27 19:08:51 ERROR on database index
Previous Message Gavin M2301 2019-03-20 04:14:12 Fixed mingw