In part 1 of this series, I went over what PostgreSQL is, how it works, how you can get started adding databases and tables to create a relational database for storing your information. In this article i’m going to assume that you have everything downloaded, and databases/tables to work with. I’m going to go through a few operations that I used to find myself constantly googling. If you find this information useful it would be a good addition to your bookmarks. Let’s begin!
How to import a CSV.
Let’s say you have a CSV file that you want to import into an postgres Database using pgAdmin. The first thing you need to do is create a table in pgAdmin, name it whatever you want, and then declare the proper amount of columns in order, corresponding to the CSV. For example, I’ll import a CSV with 2 columns: name, number. The name column will be a string, the second will be a random number.
In pgAdmin let’s create a new table, called locations, as described in part 1 of this series.
Keep in mind that we need to declare the columns and those columns need to be declared in the same order that they appear on the CSV.
When we hit save, we will have a table called(namedb) with two columns. To get the data from the CSV into the namedb table, right click on the table in pgAdmin.
Importing data can also be achieved by right clicking on the table in pgAdmin, selecting the query tool, and using this SQL statement:
COPY namedb(name, locations)
FROM '/Users/brendanferris/Desktop/scripts/postgres_blog/people.csv'
DELIMITER ','
CSV HEADER;
Adding an ID column to an existing table.
Now that we have the names and locations in the table, we want to add a id column, possibly to use as a key that would enable us to join the information with other information in other tables. To do this, lets add a _nameid column to the namedb table.
ALTER TABLE namedb ADD COLUMN name_id serial PRIMARY KEY;
Saving the result of a query into a CSV file.
Lets say we want to get all of the people in the database whose name starts with ‘A’, then save that result in it’s own CSV file. We can accomplish this by first grabbing the information we want, in this case:
SELECT name, locations FROM namedb
WHERE name ILIKE('a%');The resulting information should be displayed in your data output window.
In order to output these two rows into a CSV, we need to use the following query format:
COPY ([Query]) TO '[File Name]' DELIMITER ',' CSV HEADER;We simply need to input our query in after the COPY keyword, within parentheses, and send the file to a specific location in a specified format. Our specified format is CSV, because we declared our delimiter to be a comma.
The file can be found along whichever path you specified.
These are some of the more common operations that I have found myself performing in the past, and I hope this little rundown saves you some time in the future.
Happy coding!
💻 Feel free to check out my website.
