Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

The First Use Case

Explore how to load tab-separated CSV data into PostgreSQL, convert text data into proper SQL types, and use SQL queries with variables and intervals to retrieve data for specific periods. Understand the use of psql commands, data formatting functions, and practical integration with Python for data retrieval.

We'll cover the following...

The Intercontinental Exchange provides a chart with daily NYSE group volumes. We can fetch the Excel file, which is a CSV file that uses tab spacing as a separator, remove the headings, and load it into a PostgreSQL table.

Loading the data set

Here’s what the data looks like with comma-separated thousands and dollar signs, so we can’t readily process the figures as numbers:

2010	1/4/2010	1,425,504,460	4,628,115	$38,495,460,645
2010	1/5/2010	1,754,011,750	5,394,016	$43,932,043,406
2010	1/6/2010	1,655,507,953	5,494,460	$43,816,749,660
2010	1/7/2010	1,797,810,789	5,674,297	$44,104,237,184

We create an ad-hoc table definition, and once the data is loaded, we then transform it into a proper SQL data type, with the help of the alter table command.

PostgreSQL
begin;
create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );
-- datestyle of the database to ISO, MDY
\copy factbook from 'factbook.csv' with delimiter E'\t' null ''
alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,
   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;
commit;

We use the PostgreSQL copy functionality ...