HOW TO LOAD A SAMPLE DATABASE IN POSTGRESQL

Hi there! Today’s article will be on how to load databases into Postgresql. Why load databases into Postgresql you may ask? This is because as someone dealing with data, you may have a need to search or scrape for more data when doing analysis. This data may come in Excel file format, among others. So, it is important to have a provision for uploading these files into the Postgresql environment for easy access and analysis.

Postgresql isn’t just a database, it is also a coding language, and not just any coding language, but an SQL language. Like languages in the real world that people use for communication, Postgresql is ONE dialect of SQL. There are different variations or dialects of this same language that are used by other SQL database providers such as Microsoft SQL Server, Snowflake, BigQuery, and even Amazon Redshift.

This article will explore different approaches to loading databases or data files into SQL.

First, we’ll be using two example databases for the purposes of this article. They can be found through the link here.

Fire up the Postgresql database on the computer and right-click on Servers then Databases in the Postgres interface like in the image above. This brings up an option to “Create” or “Refresh” the database. Click on the “Create” option and then the “Database” option.  This opens up another window where there’s an option to write the name of the new database that will be created, as you see in the image below.

In this step, the database has to be created and named before any file can be uploaded into the Postgres database. In the image below, the name of this particular database being imported into Postgres is called NORTHWIND database, so that’s what we’ll name the empty database being created.

After saving the file, go to PostgreSQL 13 on the upper left-hand side of the interface and right-click on it. This should open up a drop down menu where you can click on the Refresh option.

After clicking on the Refresh option, the Northwind database will appear among the database options like in the image below.

Right-clicking on the Northwind database will open up another drop-down menu. Scroll down the options on this menu and click on Restore.

This will take you to a window where the format of the file to be imported can be specified along with the file path.

Click on the three dots encircled in red to initiate the file path where the databases have been downloaded on your system.

It is important to make sure that the folder for the compressed database files is in the format All Files, as is highlighted in red in the bottom right-hand corner. The black arrow is to highlight the end of the Northwind file path.

After that, right-click on the Northwind database, under the Database header at the top left-hand side. Click on Refresh in the drop-down menu.

To ensure that there are tables in the database, it is important to click on the Northwind database, then Schemas, after which you click on Tables. That will show a list of tables contained within the database.

LOADING CSV FILES INTO POSTGRES

The second approach to loading data into Postgres in this article is with CSV files. To do this, first create another database in Postgres. You can name it whatever you like. The database in this article will be called “orders”.

Open up the Query Tool in Postgres and type in the script for the data in your CSV file. This script will be initiated by the CREATE TABLE statement and contain the names of the respective columns along with their data types as in the image below, then press the Run/Play button.

CREATE TABLE orders (orderid smallint, shipname varchar(40))

As at this point, even though there’s an orders table in the orders database, there’s no actual data (rows and columns) in the table.

To import the CSV file, run the ‘COPY table_name FROM file path‘ statement like in the image below. The HEADER CSV phrase at the end of the statement, as in the image below, is to specify that the CSV file has a header row.

COPY orders FROM 'C:\Users\user\Downloads\o\order.csv' HEADER CSV

In summary, when working with data there could be a need to add or augment data analysis with more data. The two approaches in this article provide options to Postgres users on how they can import data that can complement whatever analysis is being carried out.