News for hackers. Stuff that matters.
Re:PostgreSQL 8.3 Released by e1ven on Monday February 04, 2008 @10:48AM (#109132)
Looking over the XML datatye [1], at first it doesn't seem that this would be very useful.. It doesn't seem to index the XML, only store it in a string-like manner..

But when I look at the XML functions [2], I can start to see where the real value is. In Chron X, we're storing vast sums of XML in SQL as strings, so being able to parse it per-element and pull them could be very helpful.

[1]http://www.postgresql.org/docs/8.3/static/datatype-xml.html

[2]http://www.postgresql.org/docs/8.3/static/functions-xml.html

Re:PostgreSQL 8.3 Released by inklesspen on Monday February 04, 2008 @11:52AM (#109154)
It doesn't index the XML because it also doesn't provide any comparison methods for XML. Largely because string comparisons on XML tend to be less than useful, I think.
Re:PostgreSQL 8.3 Released by bayareaguy on Tuesday February 05, 2008 @12:35AM (#109546)
Although you can't index xml columns directly, you can define functional indexes over them. As long as you are careful to use the same function in your where clauses the index can be used.

Here's an example taken from Nikolay Samokhvalov's XML Support in PostgreSQL paper[1].

  CREATE INDEX i_table1_xdata ON table1 
  USING btree( xpath_array(xdata, '//person/@name') ); 
[1] - http://www.pgcon.org/2007/schedule/attachments/16-xml_in_pos...
Re:PostgreSQL 8.3 Released by nradov on Monday February 04, 2008 @05:01PM (#109320)
I'm not familiar with how they've done it in PostgreSQL, but DB2 does support XML indexes. You just have to write an XPath statement to represent what is stored in the index. Then you can write an XQuery statement and the query engine will use that index. It's quite fast.