Single quotes in sql files are not escaped

[abdelhakim94]

Single quotes are not escaped in sql files, which results in errors when running the scripts.

For example: 'Hassi R'mel ' should be 'Hassi R''mel'

Tested on Postgres and oracle.

[bsaidus]

Single quotes are not escaped in sql files, which results in errors when running the scripts.

For example: 'Hassi R'mel ' should be 'Hassi R''mel'

Tested on Postgres and oracle.

Yes, I confirm !!

[Douibi]

Single quotes are not escaped in sql files, which results in errors when running the scripts.

For example: 'Hassi R'mel ' should be 'Hassi R''mel'

Tested on Postgres and oracle.

I confirm the issue on MS SQL Server

[kwaaaf]

Confirm it! What's the solution?

[abdelhakim94]

Confirm it! What's the solution?

As I said, use double quotes instead of single quotes, 'Hassi R'mel' => 'Hassi R''mel'

[kwaaaf]

Should i change them one by one? Could you just update the file? Thanks dude.

[a-elhaddad]

I used the csv file as follow

DROP TABLE IF EXISTS temp1.algeria_postcodes;
CREATE TABLE IF NOT EXISTS temp1.algeria_postcodes(
    post_code          VARCHAR(255),
    post_name          VARCHAR(255),  
    post_name_ascii    VARCHAR(255),  
    post_address       VARCHAR(255), 
    post_address_ascii VARCHAR(255),  
    commune_id         INTEGER,  
    commune_name       VARCHAR(255),  
    commune_name_ascii VARCHAR(255), 
    daira_name         VARCHAR(255),  
    daira_name_ascii   VARCHAR(255),  
    wilaya_code        VARCHAR(4), 
    wilaya_name        VARCHAR(255),  
    wilaya_name_ascii  VARCHAR(255)
);

COMMENT ON COLUMN temp1.algeria_postcodes.post_name          IS 'Name of post in arabic';
COMMENT ON COLUMN temp1.algeria_postcodes.post_name_ascii    IS 'Name of post in ASCII characters (french)';
COMMENT ON COLUMN temp1.algeria_postcodes.post_address       IS 'Address of post in arabic';
COMMENT ON COLUMN temp1.algeria_postcodes.post_address_ascii IS 'Address of post in ASCII characters (french)';
COMMENT ON COLUMN temp1.algeria_postcodes.commune_id         IS 'ID of commune';
COMMENT ON COLUMN temp1.algeria_postcodes.commune_name       IS 'Name of commune in arabic';
COMMENT ON COLUMN temp1.algeria_postcodes.commune_name_ascii IS 'Name of commune in ASCII characters (french)';
COMMENT ON COLUMN temp1.algeria_postcodes.daira_name         IS 'Name of daira in arabic';
COMMENT ON COLUMN temp1.algeria_postcodes.daira_name_ascii   IS 'Name of daira in ASCII characters (french)';
COMMENT ON COLUMN temp1.algeria_postcodes.wilaya_name        IS 'Name of wilaya in arabic';
COMMENT ON COLUMN temp1.algeria_postcodes.wilaya_name_ascii  IS 'Name of wilaya in ASCII characters (french)';

\COPY temp1.algeria_postcodes(post_code,post_name,post_name_ascii,post_address,post_address_ascii,commune_id,commune_name,commune_name_ascii,daira_name,daira_name_ascii,wilaya_code,wilaya_name,wilaya_name_ascii) FROM '/production/temp1/database/temp1/algeria_postcodes.csv'  DELIMITER ',' CSV HEADER;

ALTER TABLE temp1.algeria_postcodes ADD COLUMN id INTEGER;
CREATE SEQUENCE temp1.algeria_postcodes_id_seq OWNED BY temp1.algeria_postcodes.id;
ALTER TABLE temp1.algeria_postcodes ALTER COLUMN id SET DEFAULT nextval('temp1.algeria_postcodes_id_seq');
UPDATE temp1.algeria_postcodes SET id = nextval('temp1.algeria_postcodes_id_seq');