Setting up Local PostgreSQL Database
Last month, I create a database on Vercel Postgres to store the data in wildlifeinchina.org. However, the free version has monthly limitations on data storage and transfers. To avoid wasting online resources, I decide to set up a local PostgreSQL environment to facilitate the debugging process for the upcoming web application development.
To install PostgreSQL server, update your computer's local package cache with the latest set of packages. Afterwards, install the
By default, PostgreSQL is configured to use peer authentication, which allows users to log in if their operating system user name matches a PostgreSQL internal name.
The installation process created an operating system user called
postgres to match the
postgres database administrative account.
To log into PostgreSQL with the
psql client, use
sudo to run the command as the postgres user:
After setting password for user
postgres, you can connect to postgres with URI:
For more details, you can refer to:
The CSV file,
animals.csv, containing the data given below:
In this table, the following columns should be present:
- Animal ID: A unique animals identification number.
- Class: The animal's class.
- Order: The animal's order.
- Family: The animal's family.
- Scientific Name: The animal's scientific name.
- Common Name: The animal's common name.
- Chinese Name: The animal's Chinese name.
- Protection Class: The animal's protection class.
- Conservation Status: The animal's protection status.
- Aquatic: Whether or not the animal is aquatic.
- Endemic: Whether or not the animal is endemic.
- CITES: CITES appendices.
animals table with the above schema with the following SQL:
Now you can easily import CSV to PostgreSQL via using the
and show table contnet: