Hi!
I would like to store informations in an array field in a postgresql database.
How do I pass the array to the SQL statement?
Here is an exemple of the SQL query I need to execute is : INSERT INTO "oem".maintenance_activite VALUES(DEFAULT, 0, 'description' ,1, 0, 0, 0, 'commentaires',0,'NOM' ,'2016-01-01', '2016-01-01',ARRAY['1','2']) RETURNING maintenance_activite_id
Is my SQL string correct? : [code] Dim strSQL As String = "INSERT INTO "+ chr(34) + Module_App_Maint.DBSCHEMANAME + chr(34) + “.” + Module_App_Maint.DBTABLENAME + " VALUES(DEFAULT, $1, $2 ,$3, $4, $5, $6, $7, $8, $9, $10, $11, ARRAY[$12]) RETURNING " + Module_App_Maint.DBTABLENAME + “_id”
I agree with Norman if you add the qualifier, “ as a substitute for related tables.” But if you do have a good and valid reason for storing an array, consider using a JSON field. That’s what we’ve done in those rare times when we thought it was a good idea (typically a log or other write-once-read-thereafter type scenario).
ARRAY['1','2'] should be equivalent to '{"1", "2"}' so either you use the constructor syntax with separate bind parameters if their number is constant or you use the literal syntax with one bind parameter if you need them to be flexible.
OK! Clear enough!
Thanks for the advice! Maybe you could advice me on the best approach for my situation…
We are building an application that manages tasks created by the user to which we need to associate assets. Since each task will apply to a different number of assets from 1 to 150, I thought that having a table containing an entry for each “pair” task-asset" would become very big very fast.
We need to :
evaluate the progression of the work ( assets repaired / total task’s assets)
track which assets are still left
keep a “log” of which asset has been repared and when, by who and so on
it’s not a problem to have a table with a very big number of items in it
I read here I think some postgres database with billions of records ?
it is a problem to have a database with bad defined or no relations between tables.
Three tables: asset (a list of all available assets), task (a user’s task), task_asset (a table that links a task.id to an asset.id). You can use a UNIQUE INDEX to make sure that the same asset is not assigned to a task twice (unless that’s allowable, of course). Your SQL to list tasks with assets would look something like this:
SELECT
*
FROM
task
LEFT JOIN task_asset ON task.id = task_asset.task_id
LEFT JOIN asset ON task_asset.asset_id = asset.id