Escaping single quotes in REDSHIFT SQL

Question

I've lots of string values containing single quotes which I need to insert to a column in REDSHIFT table. I used both /' and '' to escape the single quote in INSERT statement.

e.g.

INSERT INTO table_Temp
    VALUES ('1234', 'O\'Niel'), ('3456', 'O\'Brien')

I also used '' instead of \' but it keeps giving me error that "VALUES list must of same length" i.e. no: of arguments for each record >2.

Can you let know how to have this issue resolved?

Solution 1

The standard in SQL is double single quotes:

INSERT INTO table_Temp (col1, col2)  -- include the column names
    VALUES ('1234', 'O''Niel'), ('3456', 'O''Brien');

You should also include the column names corresponding to the values being inserted. That is probably the cause of your second error.

Solution 2

You could use CHR(39) and concat the strings. Your name would look like below:

  ('O' || CHR(39)||'Brian')

Comments

[stack_pointer is EXTINCT]

The table only has 2 columns and both are VAR CHAR. I've tried with double single quotes as well. It's not working.

[Gordon Linoff]

@stack_pointerisEXTINCT . . . This is quite strange. Are you sure you are using the "simple" single quote and not some fancier character?

[stack_pointer is EXTINCT]

I noticed a different problem was there because of commas used within the names and it was a CSV file.

[szeitlin]

This is not something you can do in Redshift.