How can I import data with referenced attributes using postgreSQL/pgadmin?
Whenever I try to insert data that are reference attributes (either using _insert_ or _copy_) I get that 0 rows are affected, thus not importing anything.
When trying to insert/copy regular attributes, everything goes fine.
Also, I've managed to insert 1 table with references via that method, but only 44/60 rows were imported.
I really need to import that way because I have a really large CSV file with data and it takes way too long to do it via CMDBuild.
What are the alternatives?
CMDBuild manage the reference attributes storing in the database the id of the linked record.
Then you can run SQL “insert” commands that operate in this way.
In general, however, we do not recommend the insertion of data directly in the database because this can bypass the control mechanisms implemented in CMDBuild, causing problems sometimes difficult to solve.
CMDBuild Team
1 Like
Previously Tecnoteca wrote:
CMDBuild manage the reference attributes storing in the database the id of the linked record.
Then you can run SQL "insert" commands that operate in this way.
In general, however, we do not recommend the insertion of data directly in the database because this can bypass the control mechanisms implemented in CMDBuild, causing problems sometimes difficult to solve.
CMDBuild Team
Thank you for the reply, I've managed to figure out what was the problem.
CMDBuild uses pre-inherited Attributes when creating another table; "Code" and "Description". When using the type 'REFERENCE' for other attributes, when importing, you need to fill a number equal to the "Code" attribute so CMDBuild returns what's in the "Description" attribute.
But there is another inherited attribute hidden to CMDBuild that it is needed to use on the import through postgreSQL, the column "Id". By replacing that I've managed to import all data quicker through postgreSQL.
On a side note, the best way to import CSV files is by using the COPY statement:
COPY "Table_Name"("Column1","Column2","Column3","ColumnX....") from 'C:\csvfile.csv DELIMITER ';' HEADER CSV;
- The quotes are to consider case sensitive.
- The columns in the ( ) should be in order on which they are ordered on your CSV.
DELIMITER - should be replaced with what it is using to separate all the data (open the CSV file with notepad to check)
HEADER - is a boolean type with "true" as default. Only add this statement if your CSV has headers typed in (i.e. Code;Description;Interface;Size;Active)