Store an array in PostgreSQL

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”

pgps.bind(0, 0)

pgps.bind(11, ???)

[/code]

Thanks!

Yeah dont do that
Arrays should not exist in a properly modelled sql database
Really

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).

Stressing again that this should be rare.

have a look to http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT for the possible syntax of array literals.

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

as in “never” since they aren’t decently searchable if you ever decide you need to do this

Got it…

Thanks everyone! Sorry for my late answer, it seems that I wasn’t noticed about the latest updates …

ARRAYs present in SQL Standard.
And therefore they are a valid tool if a DBMS supports it.

A difference of ARRAY from a related table, for example, in

  • items are ordered and you have access by index.
  • ARRAY should have restriction on size, although postgreSQL ignores size of array.

Search yes, unlikely indexed, but exists tasks which do not need it, e.g.
ChessBoard SHORT[8][8]

Can be found similar questions and answers for other languages, e.g. Java
https://stackoverflow.com/questions/18658107/binding-parameter-as-postgresql-array