● videoINMS Data Import
Transformation, Part 1

Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.
SECTION
INMS Data Import
NEXT UP
Concept: Constraints
COURSE
PostgreSQL Fundamentals
28 lessons
About this lesson
Transforming CSVs (aka "text flat files") into relational data is both challenging and fun. Let's impose some typing and rules for the incoming data so that we can rely on it in our analysis:
<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">'enceladus'</span>;
<span class="hljs-keyword">create table</span> inms(
id bigserial <span class="hljs-keyword">primary key</span>,
created_at <span class="hljs-type">timestamp</span> <span class="hljs-keyword">not null</span>,
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">></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">'osi'</span>,<span class="hljs-string">'csn'</span>,<span class="hljs-string">'osnb'</span>,<span class="hljs-string">'osnt'</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">>=</span><span class="hljs-number">0.125</span> <span class="hljs-keyword">and</span> mass <span class="hljs-operator"><</span> <span class="hljs-number">100</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">></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">></span> <span class="hljs-number">0</span>)
);
Does this look overwhelming to you? That's OK - in the next video we're going to break things down a bit.
Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.