title: Copying a snowflake table with nulls and empty strings to csv that can be imported with psql copy command
body: <p>So, if you have this table in Snowflake:</p>
  
  <p><code>create table t (x string, y string) as select '', null;</code></p>
  
  <p>and you copy it to an external stage with file_format csv, you get this error if you don't set field_optionally_enclosed_by to something other than none:</p>
  
  <p>Cannot unload empty string without file format option field_optionally_enclosed_by being specified.</p>
  
  <p>so, let's say it's set to '"'.</p>
  
  <pre><code>create stage some_stg
  url='s3://&lt;some-bucket&gt;/&lt;some-dir&gt;'
  file_format = (type = csv field_optionally_enclosed_by='"' compression = none)
  credentials = (aws_role = '&lt;your-arn-for-snowflake&gt;')
  </code></pre>
  
  <p>I'm sure this issue reproduces with an internal stage if you don't want to mess with getting snowflake to use your s3 bucket.</p>
  
  <p>When you run a copy for table t above:</p>
  
  <p><code>copy into @some_stg/t.csv from t overwrite = true;</code></p>
  
  <p>you get a file (t_0_0_0.csv) that looks like this:</p>
  
  <p><code>"","\N"</code></p>
  
  <p>And after creating the equivalent table in postgres:</p>
  
  <p><code>create table t (x varchar, y varchar);</code></p>
  
  <p>When you load that into postgres with psql copy like this:</p>
  
  <p><code>psql -h &lt;host&gt; -U &lt;user&gt; -c "copy t from stdin with csv null '\N'" &lt; t_0_0_0.csv</code></p>
  
  <p>The contents of t on postgres is:</p>
  
  <pre><code>x, y
  "","\N"
  </code></pre>
  
  <p>Now this make sense because snowflake put the \N in double quotes, so the psql copy preserved it. If you edit t_0_0_0.csv and remove the double quotes around the \N:</p>
  
  <p><code>"",\N</code></p>
  
  <p>And run psql copy again then the \N is correctly converted to null</p>
  
  <p>There does not appear to be a way to generate a csv file from snowflake that supports empty string and null that can be preserved loading into postgres. I messed with the snowflake configs EMPTY_FIELD_AS_NULL and NULL_IF which in snowflake's documentation it even speaks to this issue:</p>
  
  <pre><code>When unloading empty string data from tables, choose one of the following options:
  
  Preferred: Enclose strings in quotes by setting the FIELD_OPTIONALLY_ENCLOSED_BY option, to distinguish empty strings from NULLs in output CSV files.
  </code></pre>
  
  <p>It does "distinguish" them but not in a way that psql copy can use without manipulating the file with sed beforehand. </p>
  
  <p>Does anyone know how to generate a snowflake csv that preserves empty strings and nulls in a way that psql copy can reproduce?</p>