Hulver's site || Parkdale sketch #5

Diaries
Everything
Who's Online?
FAQ / Help

Parkdale sketch #5

By spacejack (Wed Jun 28, 2006 at 06:42:22 PM EST) Parkdale, postgres (all tags)

Also a question for postgres geniouses.

On King Street west of Dufferin:

And the postgres question: Anyone know how one would go about creating an array in a table for which each element must reference an ID of a field of another table?

< Get Hot or Go Home | BBC White season: 'Rivers of Blood' >

Related Links

- spacejack's Diary

Google

Comment Controls

Parkdale sketch #5 | 12 comments (12 topical, 0 hidden) | Trackback

Is there a reason you can't just use a foreign key by Driusan (4.00 / 1) #1 Wed Jun 28, 2006 at 06:47:35 PM EST

Or does that expect the array as an object to reference another table (which doesn't make much sense to me) rather than each individual element? I'm not all that familiar with arrays in relational database.

I do want a foreign key by spacejack (2.00 / 0) #2 Wed Jun 28, 2006 at 06:51:56 PM EST

But I want an array of them :) Each record should be able to have any number of references to records in another table, not just one.

I've handled similar cases in the past by using another table, but I notice postgres has arrays and thought this might be a better way to handle it.

[ Parent ]

My solution would be: by Driusan (4.00 / 1) #5 Wed Jun 28, 2006 at 07:07:05 PM EST

1. Try just creating it with foo integer[] references blah.
2a. If that works, rejoice.
2b. If it fails, and you're feeling ambitious
2bi. look into the trigger that Postgres makes for a non-array foreign key. If any obvious way to modify that and make your own array version jumps out at you, try it.
3. If you're either feeling intelligent enough to realize it's not worth your time, or feeling lazy, then after 1, give up and use a join instead of an array.

I've never had much luck getting an array (with or without foreign keys) to work in Postgres, but to be honest I haven't tried that hard as my above flowchart might imply.

[ Parent ]

: 2a fails it by spacejack (2.00 / 0) #7 Wed Jun 28, 2006 at 07:11:29 PM EST

Not ambitious enough to try 2bi.

[ Parent ]

Fabian Pascal in da house by komet (4.00 / 3) #3 Wed Jun 28, 2006 at 06:54:07 PM EST

Arrays in relational dbs are evil. Do not use this Postgres feature; solve your problem the proper way, using an intermediate join table.

--
<ni> komet: You are functionally illiterate as regards trashy erotica.

I kind of figured that by spacejack (2.00 / 0) #4 Wed Jun 28, 2006 at 06:58:35 PM EST

But maybe there's an advocate of arrays who'll make a rebuttal... :)

[ Parent ]

I doubt it by komet (4.00 / 4) #6 Wed Jun 28, 2006 at 07:07:09 PM EST

Arrays are perhaps ok for a small number of values that are naturally arrays. For example, I have seen PG databases store coordinates, vectors and matrices as arrays. But if anyone has ever advocated putting keys in an array (and I haven't ever heard of such), surely Fabian Pascal and zombie Codd have hunted them down and killed them by now.

--
<ni> komet: You are functionally illiterate as regards trashy erotica.
[ Parent ]

alas ... by BlueOregon (4.00 / 1) #8 Wed Jun 28, 2006 at 07:42:55 PM EST

... google returns no results for "gallery of bad database design" or "museum of bad database design" and "bad database design" returns too many results, but "worst database design" provide a few nice gripes. It is a shame that "evil database design" is also empty, so all I could do was return to the Fabian Pascal Vociferous Ignorance Hall of Shame.

_
"The german quoting guy is a little bit out there." (fleece)
[ Parent ]

: The Daily WTF... by crux (4.00 / 1) #12 Thu Jun 29, 2006 at 01:30:26 PM EST

... has database stuff ocassionally, here.

[ Parent ]

Day-yam! by greyrat (4.00 / 2) #9 Wed Jun 28, 2006 at 08:44:53 PM EST

Love the clouds especially! Did the cops come by and say "Get up off the street and move along you bum!"
~
There is absolutely no correlation or causation amongst intelligence, power, talent and wealth.
Kha-Nyou

They were busy by spacejack (4.00 / 1) #10 Wed Jun 28, 2006 at 09:10:07 PM EST

sorting out a fight half a block away.

[ Parent ]

: Whatever was going on by calla (4.00 / 3) #11 Wed Jun 28, 2006 at 10:24:46 PM EST

contributed to the sketch.
Great technique and superb execution!!!
"Are Linux chicks worth it?" fencepost
[ Parent ]

Parkdale sketch #5 | 12 comments (12 topical, 0 hidden) | Trackback


Make a new account
Username:
Password: