Troubleshooting

I've never created a transformation/import script that worked right the first time - which makes me happy! These scripts reflect your assumptions and understanding of your data and if you've goofed up, you'll know!

Here's the final, fixed script:

<span class="hljs-keyword">drop</span> schema if <span class="hljs-keyword">exists</span> enceladus cascade;
<span class="hljs-keyword">create</span> schema enceladus;
<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">&#x27;enceladus&#x27;</span>;

<span class="hljs-keyword">create table</span> inms(
  id serial <span class="hljs-keyword">primary key</span>,
  created_at <span class="hljs-type">timestamp</span> <span class="hljs-keyword">not null</span>,
  <span class="hljs-type">date</span> <span class="hljs-type">date</span> <span class="hljs-keyword">not null</span> generated always <span class="hljs-keyword">as</span> (created_at::<span class="hljs-type">date</span>) stored,
  <span class="hljs-keyword">year</span> <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span> generated always <span class="hljs-keyword">as</span> (date_part(<span class="hljs-string">&#x27;year&#x27;</span>, created_at)) stored,
  flyby_id <span class="hljs-type">int</span> <span class="hljs-keyword">references</span> flybys(id),
  altitude <span class="hljs-type">numeric</span>(<span class="hljs-number">9</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(altitude <span class="hljs-operator">&gt;</span> <span class="hljs-number">0</span>),
  source text <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(source <span class="hljs-keyword">in</span>(<span class="hljs-string">&#x27;osi&#x27;</span>,<span class="hljs-string">&#x27;csn&#x27;</span>,<span class="hljs-string">&#x27;osnb&#x27;</span>,<span class="hljs-string">&#x27;esm&#x27;</span>)),
  mass <span class="hljs-type">numeric</span>(<span class="hljs-number">6</span>,<span class="hljs-number">3</span>) <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(mass <span class="hljs-operator">&gt;=</span><span class="hljs-number">0.125</span> <span class="hljs-keyword">and</span> mass <span class="hljs-operator">&lt;</span> <span class="hljs-number">256</span>),
  high_sensitivity_count <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(high_sensitivity_count <span class="hljs-operator">&gt;=</span> <span class="hljs-number">0</span>),
  low_sensitivity_count <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(low_sensitivity_count <span class="hljs-operator">&gt;=</span> <span class="hljs-number">0</span>),
  imported_at timestamptz <span class="hljs-keyword">not null</span> <span class="hljs-keyword">default</span> now()
);

<span class="hljs-keyword">update</span> inms
<span class="hljs-keyword">set</span> flyby_id <span class="hljs-operator">=</span> flybys.id
<span class="hljs-keyword">from</span> flybys 
<span class="hljs-keyword">where</span> flybys.date <span class="hljs-operator">=</span> inms.date;

<span class="hljs-keyword">create table</span> chemistry(
  id serial <span class="hljs-keyword">primary key</span>,
  name text <span class="hljs-keyword">not null</span>,
  formula text <span class="hljs-keyword">not null</span>,
  molecular_weight <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span>,
  peak <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span>
);

<span class="hljs-keyword">copy</span> chemistry(name, formula,molecular_weight, peak) 
<span class="hljs-keyword">from</span> <span class="hljs-string">&#x27;[Absolute path to]/csvs/chem_data.csv&#x27;</span>
<span class="hljs-keyword">with</span> delimiter <span class="hljs-string">&#x27;,&#x27;</span> header csv;