From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rita <rmorgan466(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: using hstore to store documents |
Date: | 2017-01-29 22:42:00 |
Message-ID: | f1cb001f-deac-5b95-5c59-20c96cf18301@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 ('
>
>
> <attendee>
>
>
> <bio>
>
>
> <name>John Doe</name>
>
>
> <birthYear>1986</birthYear>
>
>
> </bio>
>
>
> <languages>
>
>
> <lang level="5">php</lang>
>
>
> <lang level="4">python</lang>
>
>
> <lang level="2">java</lang>
>
>
> </languages>
>
>
> </attendee>', 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
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Thu, Jan 26, 2017 at 5:37 AM, Rita <rmorgan466(at)gmail(dot)com
> <mailto:rmorgan466(at)gmail(dot)com>>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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Mills | 2017-01-29 23:12:36 | Re: Recovery Assistance |
Previous Message | Adrian Klaver | 2017-01-29 17:49:47 | Re: Recovery Assistance |