Integrate DEAR Inventory with PostgreSQL

In this section of my article, I will explain what DEAR Inventory is and why I want to use it with PostgreSQL.

DEAR Inventory

DEAR Inventory is a Web-based inventory management software that allows users to track inventory, maintain physical assets, and manage product information all in one place. DEAR Inventory is available for free and can be used for free on a single computer. However, the free version has many restrictions and only allows users to maintain up to five products. If you have more than five products, you will have to upgrade to a paid version of DEAR Inventory.

PostgreSQL

PostgreSQL is an open source relational database system. It is often used for storing data and as a back-end for Web applications. PostgreSQL uses Structured Query Language (SQL. to create, read, update and delete data from databases. These databases can be accessed by other computer programs or by a human using a Web browser. PostgreSQL is capable of storing large amounts of data and performs well under stress. It also scales well with extremely large datasets.

In this section of my article, I will explain how DEAR Inventory can be integrated with PostgreSQL.

Integration of DEAR Inventory and PostgreSQL

I will integrate DEAR Inventory with PostgreSQL because it will allow me to transfer my data from one system to another. The integration process will be done by creating a DEAR Inventory table in PostgreSQL and importing the records from DEAR Inventory into it. Then, I will export the data from PostgreSQL and import it into DEAR Inventory. The steps below show how I will do this:

• The first step is to create a new table in Postgres called dearm_inventory . This table will map directly to the dearm_inventory table in DEAR Inventory. Therefore, each field in the table must have a corresponding field in the other table. However, the primary key is different between the two tables so it must be added to the PostgreSQL table. Since the primary key in DEAR Inventory is ID, I will add an ID field to the PostgreSQL table.
• The second step is to import the data from DEAR Inventory into PostgreSQL by using the COPY command. First, I need to create a file containing the data that will be imported into PostgreSQL. This file needs to contain one row per product with the corresponding fields specified in the dearm_inventory table. In this case, there are only two fields. Name and Price . Once the file is created, I can use the COPY command to import the data from DEAR Inventory into PostgreSQL. COPY dearm_inventory (Name, Price. FROM '/home/dbadmin/dearm_inventory.csv';
• The third step is to export all of the data from PostgreSQL and import it into DEAR Inventory by using the IMPORT command through SQL. To perform this step, I need to change the delimiter from a comma ( , . to a tab ( TAB . in order to match the format of DEAR Inventory's dearm_inventory table. Then, I can use a SELECT query with a GROUP BY clause to select all of the records from PostgreSQL and output them into a CSV file. SELECT * INTO '/home/dbadmin/dearm_inventory_data.csv' FROM dearm_inventory GROUP BY Name; When this command finishes running, I should have a CSV file saved in my home directory containing all of the records from PostgreSQL's dearm_inventory table.
• The fourth step is to import all of the data from PostgreSQL into DEAR Inventory by using the IMPORT command through SQL. To do this, I need to change the delimiter from a tab ( TAB . to a comma ( , . in order to match the format of DEAR Inventory's dearm_inventory table. Then, I can use a SELECT query with a GROUP BY clause and some WHERE clauses to select all of the records from PostgreSQL and output them into a CSV file. SELECT * INTO '/home/dbadmin/dearm_inventory_data.csv' FROM /home/dbadmin/dearm_inventory_data WITH DELIMITER ',' REPLACE ',', ''; When this command finishes running, I should have a CSV file saved in my home directory containing all of the records from PostgreSQL's dearm_inventory table.
• The fifth step is to import all of the data from PostgreSQL into DEAR Inventory by using the IMPORT command through SQL again but with additional WHERE clauses that match criteria in both databases. This time, instead of just selecting all of the records from PostgreSQL's dearm_inventory table, we are going to select only those records that match criteria in both databases. SELECT * INTO dearm_inventory FROM dearm_inventory_data WHERE Name = 'Widget 1' AND Price >= 0; After we have finished running this query, we should have one record imported into DEAR Inventory's dearm_inventory table that matches our criteria. We can then run this query again but replace Name with Widget 2 and Price >= 0 with Price <= 30 . This time we should have four imported records because they match our criteria on two fields. We could continue repeating until we have all of our records imported into DEAR Inventory but that may take too long without any improvements to our queries or indexing structure on either database!

Benefits of Integration of DEAR Inventory and PostgreSQL

The process to integrate DEAR Inventory and PostgreSQL may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.