setup – Nest Cafe ~ Cafe Nido

This is part 2 in continuation from the post Your Personal Database.

Now that we have a personal SQL database set up in DataGrip, let’s import our first data table. Note that this is for a personal database set up on a local computer. Not a shared database connected to an online server (which is what most companies or organizations would use).

Here’s how to import a table:

Step 1: Find the “public > tables” folder to which tables will get saved.
Starting with a fresh PostgreSQL database set up, this was located in postgres > public > tables.
If you do not see a “tables” folder, then use “public”. The tables folder will get automatically created upon importing your first table.
Do not use “Database Objects” or “Server Objects”.

Step 2: Right click on “public” or “public > tables” folder > Import/Export > Import Data from File(s).

Step 3: Select the data file to be imported as a table, then ‘OK’.
Make sure the file is closed. For example, do not have the .xlsx or .csv open in Excel on your computer, or else you will get an error.
Note how many rows of data the original file has (will use for validation in step 5).

Step 4: Set import format settings and set up SQL table.
Select the file format (top left corner).
Check “First row is header” if it applies (this is not checked by default).Z
Set the SQL table name (top right).
Review the header names (middle right). Double click on each and rename column names to lowercase with underscores replacing spaces in order to avoid using quotes ” to reference column names in SQL queries. You don’t need to redo this step when importing new data into this table in the future (but you can go back and edit).
Click “Import”.

Step 5: Validate that all data rows were imported.
A popup will appears in the bottom right corner showing how many rows were imported, and if any, how many errors were written.
Check #1: The number of rows imported should match what you expect from the original data file. For example, my data has 64 rows in the original CSV – (1) header row, and (63) data rows. So I expect 63 rows to be imported to the table.
If there were any errors, they were not imported into the data table. Investigate, fix, and re-import.

Step 6: Verify that the data looks right.
The newly imported table now appears under the “tables” folder on the top left corner.
Double click on this to view the table within DataGrip. Check that the data looks correct and as you expect.
Issues might include:
– Dates are blank or missing values (check that they have the right data type in Step 4, ie Date or Text)
– Too many rows: Old data on the table was not deleted, and newly imported data was appended on instead of replacing the old data

Step 7: Test it out!
See if it works!

SELECT * FROM new_table;

Happy SQL querying!

I was reading Choose FI: Your Blueprint to Financial Independence and one of the chapters concluded with a question like:
“What would you do if you didn’t have to work?”

Something rose to the surface. Even if I didn’t need to work to earn money, I would still practice data analysis using SQL.

This awakened my desire to set up a SQL server-database for personal use. Back-end database access where I can write queries. I miss this dearly from my previous job, where I had an in-house electronic record system and superuser access. I’ve tasted the forbidden fruit and cannot go back to measly front-end, web-browser button clicking to configure reports with limited functionality and flexibility. The power of back-end querying is what I seek, but this is challenging when my company doesn’t currently have a database. Setting one up is notoriously hard, even for professional developers.

I emerged through some struggles to set up a personal SQL database so I can practice queries with my own data. I like the IDE called Datagrip by Jetbrains (free with a student email address) and PostgreSQL (also free) which is what I used in the previous job. Here’s how to set it up.

Step 1: Download PostgreSQL
It’s free.
https://www.postgresql.org/download/

Step 2: Install PostgreSQL and set up postgres User Password and Port.

The super user credential will be used to set up the database connection in the IDE.
Username is postgres (by default). You define the Password.

The default port of 5432 worked for me and should work for most people.

Step 3: Complete PostgreSQL Installation. Restart computer to apply downloaded updates.

Step 4: Download and set up DataGrip.
It’s free with a student email account. There are other free IDEs such as DBeaver too.
https://www.jetbrains.com/help/datagrip/postgresql.html

Step 5: Set up the database in DataGrip.
In the “Database” pane on top left, click the + icon > new Data Source > PostgreSQL.

Give it a name. I called it Personal Postgres.

Use localhost, port 5432, and Authentication type as User & Password.
Enter the User: Postgres and the Password you defined in step 2. Choose your Save password preference (Forever is convenient for a personal computer).

Test the connection. If it works, then hit Apply and OK.

Note: If you get an error message like this, that means the PostgreSQL was not installed correctly (step 2).
You MUST use the username and password. The “No Auth” feature did not work for me.

Step 6: Savor the connection!
The database will take a few minutes to connect to an online server so that you can use PostgreSQL SQL functions. If you have very strict firewall settings on your computer, you might need to allow Windows firewall or similar to allow the 5432 port connection.

If everything is good, you’ll get a small Connected status update on the bottom right Event Log:

In a future post, I’ll share how to upload your first database table from a CSV file.

Happy querying!

Tag: setup

How to Import Tables with DataGrip

Your Personal Database (PostgreSQL)