Arrays in Postgres
craigkerstiens.com
Arrays in Postgres
1–10 of 40 posts
Re: Arrays in Postgres
#2Excellent article on Arrays. I wish I knew about these before, there have been so many times I should have used this instead of serializing / deserializing JSON myself via TextFields :(
Re: Arrays in Postgres
#3Excellent article on Arrays. I wish I knew about these before, there have been so many times I should have used this instead of serializing / deserializing JSON myself via TextFields :(
Postgresql 9.2 supports json, btw!
You can store json, or, even better, you can write sql that returns an arbitrary json structure.
So you can have one sql query return a nested array of hashes of arrays of hashes... handy if you need a retrieve a lot of different data at once that doesn't fit into a neat set of rows.
Re: Arrays in Postgres
#4Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.
Re: Arrays in Postgres
#5Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.
While I probably wouldn't store things in an array, it's useful to get data back out in array format sometimes.
select array_agg(email_address), home_state from users group by home_state
Re: Arrays in Postgres
#6I used Postgres arrays in a project a few years ago -- The university course catalog was so awful I had to create my own. There was a 7-element array of integers (one for each day of the week) where each element was a bitmap indicating if the class was active in that period.
To the DB driver, it was just a string -- { 0, 0, 0, 0, 0, 0, 0} -- so I split it into an array manually in client code.
Re: Arrays in Postgres
#7One of the differences with postgres is that it's OK to use interesting data types. Other systems treat it as though it were somehow wrong.
See my post here: http://thoughts.davisjeff.com/2009/09/30/choosing-data-types...
And no, using arrays is not an automatic violation of first normal form.
Re: Arrays in Postgres
#8The MADlib add-on out of UC Berkeley for Postgres and Greenplum uses arrays for inputs into its algorithms. MADlib.net is worth checking out for some in-database analytic and machine learning goodness. It's not going to replace R or python but it has potential as part of your toolbelt.
Re: Arrays in Postgres
#9One of the differences with postgres is that it's OK to use interesting data types. Other systems treat it as though it were somehow wrong. See my post here: http://thoughts.davisjeff.com/2009/09/30/choosing-data-types... And no, using arrays is not an automatic violation of first normal form.
Arrays are actually pretty cool in PostgreSQL and keep getting cooler. Unnest() turns arrays into relations for example.
One of my primary uses for arrays is for passing data to/from the application. Data may not be stored in the db as an array, but it really makes passing complex data structures to/from stored procedures a lot easier.
Re: Arrays in Postgres
#10Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.
Foreign keys as arrays is just wrong.
However, there are many cases where arrays are actually extremely useful, including the ability to be a useful intermediary type for stored procedure interfaces. For example, you can aggregate arrays of rows, and pass those into another stored procedure for processing, or you can use them for pulling info to/from your application. We do this extensively in PostgreSQL in part because DBD::Pg has excellent array support.
These are actually remarkably useful in PostgreSQL. Of course like any advanced feature it can be abused.