unload data to s3 with new line characters redshift
You should be removing escape, and just try ADDQUOTE Option, it will produce correct CSV. As "" will instruct your CSV reader to treat \n as verbiage rather than newline.
UNLOAD ('SELECT * FROM table where id = 4') TO 's3://path/temp/table1'credentials aws_access_key_id="ahsvdgsfadhsagdffjh; aws_secret_access_key=ahgsdasdhgsahdgsahdgsahdgsahgsa' delimiter '|' NULL AS '\\N' ADDQUOTE;