Hi,
On 11 April 2012 17:15, Sidney Cadot <sidney@jigsaw.nl> wrote:
> I have written code to extract these positions, and now I want to put
> them into a Postgres database. Specifically, I want to do this in a
> way that allows *fast* lookups of positions, e.g. "give me all
> positions that have a White King on c4 and either a Black Bishop or
> White Knight on f7".
I would try to use single table with 16 columns like:
white_pawn char(2)[] -- like {'c1', 'd3', ... }, max 8 elements
white_rook char(2)[] -- max 2 elements
white_bishop char(2)[] -- max 2 elements
white_knight char(2)[] -- max 2 elements
white_queen char(2)
white_king char(2)
black_pawn_1 char(2)[]
...
black_king char(2)
and each column; char(2) and char(2)[] should have btree and GiST
index respectively. The query should looks like this:
select * from positions where white_king = 'c4' and (white_bishop &&
ARRAY['f7'] or white_knight && ARRAY['f7'])
Another alternative might be to use hstore (and GiST index):
http://www.postgresql.org/docs/9.1/static/hstore.html
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)