Re: [GENERAL] using hstore to store documents

On 01/28/2017 05:57 PM, Rita wrote:
> sorry for the late reply.
>
> My table schema is very simple
>
> DROP TABLE xmltest;
>
>
>
>
>
> create table xmltest(
>
>
>   id serial,        -- dont really need the serial

Maybe not a serial id, but a Primary Key of some sort would help with 
what you say you want to do below, I think.

>
>
> data xml NOT null
>
>
> );
>
>
>
>
>
>
>
>
>
>
>
> INSERT INTO xmltest (data, id) VALUES ('
>
>
> 
>
>
>  
>
>
>  John Doe
>
>
>  1986
>
>
>  
>
>
>  
>
>
>  php
>
>
>  python
>
>
>  java
>
>
>  
>
>
> ', 1);
>
> I really don't need the serial but every 30 seconds or so I plan to
> overwrite the data portion. so, I suppose I wanted a simple key/value
> thats where my orignal question stemmed from.

Why do you want to overwrite the data if you plan to refer to it below?

>
> After xmltest has been populated, I can run xpath and unest to get my
> data into a row but I would like to store that result in another table,
> I am guessing I should look into triggers for something like that?
> Eventually, I plan to have 5-6 downstream tables which will have xmltest

Why 5-6 tables?

Are they each holding some subset of data?

A schematic representation of what you are thinking of doing would help 
with developing an answer to your question.

> as my head. The application will be accessing the downstream tables and
> rarely be touching xmltest (head table).
>
>
>
>
> On Thu, Jan 26, 2017 at 10:38 AM, David G. Johnston
> > wrote:
>
>     On Thu, Jan 26, 2017 at 5:37 AM, Rita      >wrote:
>
>         of course, sorry for being vague.
>
>         I have an external process generating a XML file (every 30 secs)
>         which is about 10MB. I would like to store the file as XML type
>         for me to query using xpath. I plan to query it every few
>         seconds by few hundred clients. so, it maybe easier for me
>         create a separate table of my xpath results and have clients
>         query that table (xpath can be expensive).
>
>
>     ​If the XML being generated has a fixed structure/schema I
>     personally would treat the XML as a serialization format and
>     de-serialize and store it in a database as one or more relationally
>     linked tables.​  If you have to deal with the possibility of dynamic
>     structure I would still try to put the fixed items into individual
>     columns and then and then any dynamic items could be stuffed into an
>     hstore typed table.
>
>     My answer to your stated question is: what happened when you tried
>     doing that?  Documentation and a bit of experimentation goes a long
>     ways in learning.
>
>     David J.
>
>
>
>
> --
> --- Get your facts first, then you can distort them as you please.--


-- 
Adrian Klaver
adrian.klaver@aklaver.com