Обсуждение: Is this doable using Postgresql crosstab or some otherfunction?
Hi folkes, this is my first message on this list. I have quite a challenging problem and my own skills seem not to be adequate for resolving it. I have a relational model where the basic idea is to store data vertically instead using traditional horizontal approach. So instead of having a row in a table with n columns, I have n rows with value columns for different data types. This makes the model very dynamic but also difficult to use. I try to describe the (simplified) core of the model (or at least the parts which have some meaning) in the following pseudo definition: Table values ID serial pk instanceID integer fk1 (never mind this) parametertypeID integer fk2 value_integer integer value_varchar character varying value_date date stamp timestamp Table parameters parametertypeID serial pk typeid integer fk1 parameternameID integer fk2 Table parameternames paramaternameID serial pk parametername character varying parameterdatatype integer or like enum(1,2,3) (this defines whether to use value_integer, value_varchar or value_date) From these three tables I would like to create a select statement where the response is the following (where parameters.typeid = x and values.timestamp = dd.mm.yyyy hh.mm.ss.xx) instanceID integer parameternames.parametername#1, value and datatype from value_integer, value_varchar or value_date parameternames.parametername#2, value and datatype from value_integer, value_varchar or value_date ... parameternames.parametername#n, value and datatype from value_integer, value_varchar or value_date Quite challanging, right? - mika -
On 04/10/12 1:52 AM, mika@digikartta.net wrote: > > Quite challanging, right? yes. and quite contrary to the relational data model. whats the point of going to all this abstraction? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Apr 10, 2012, at 1:52, <mika@digikartta.net> wrote: > > Quite challanging, right? > Aside from the possibly extremely rare case where this is actually functional the only challenging task is to tell off whomevergave you the idea/requirement that your schema is desirable - even if that person is yourself. That aside, judicious use of CASE constructs, and probably UNION, will get you your answers. Keep in mind that you likelyneed to end up casting all of your values to varchar/text in the end. You may also want to look at the HSTORE extension. Given the generic nature of your inquiry actual specific advice is impossible to give. David J.
Let's say I have tens or hundreds or thousands of feature (instance) types. Each of them would need its own table. Let's say I give for the application administrator, a possibility to create new feature types? He/she merely knows what's the database is. So I need a model which is capable for offering "dynamic table structures". I already have implemented parts which allow creating these types, creating instances of them and populating those instances with data. Now I have to come up with a method for flattening that data into one view so that it can be used directly. - mika - On Tue, 10 Apr 2012 01:58:46 -0700, John R Pierce <pierce@hogranch.com> wrote: > On 04/10/12 1:52 AM, mika@digikartta.net wrote: >> >> Quite challanging, right? > > yes. > > and quite contrary to the relational data model. > > whats the point of going to all this abstraction? > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast
This schema is in use in one quite big system, which utilizes different DB than Postgresql. It is fully functional. - mika - On Tue, 10 Apr 2012 02:19:14 -0700, David Johnston <polobo@yahoo.com> wrote: > On Apr 10, 2012, at 1:52, <mika@digikartta.net> wrote: > >> >> Quite challanging, right? >> > > Aside from the possibly extremely rare case where this is actually > functional the only challenging task is to tell off whomever gave you > the idea/requirement that your schema is desirable - even if that > person is yourself. > > That aside, judicious use of CASE constructs, and probably UNION, > will get you your answers. Keep in mind that you likely need to end > up casting all of your values to varchar/text in the end. > > You may also want to look at the HSTORE extension. > > Given the generic nature of your inquiry actual specific advice is > impossible to give. > > David J.
On Tue, Apr 10, 2012 at 4:27 AM, <mika@digikartta.net> wrote: > > Let's say I have tens or hundreds or thousands of feature (instance) types. > Each of them would need its own table. > Let's say I give for the application administrator, a possibility to create > new feature types? He/she merely knows what's the database is. > So I need a model which is capable for offering "dynamic table structures". > > I already have implemented parts which allow creating these types, creating > instances of them and populating those instances with data. Now I have to > come up with a method for flattening that data into one view so that it can > be used directly. If you want a schemaless design in a relational database, you have a some options: EAV model, hstore, xml (soon json too) as data. I consider these to be niche solutions not well suited to general purpose data management. For the most part, SQL really only works properly with a well defined schema. Your incoming data looks to be EAV-ish. You can build horizontal structures with crosstab and what you're trying to do looks semi doable, but it's going to to be quite difficult. merlin
Merlin, thanks for your reply. Semidoable? Let's break this into parts: 1) Is it possible to create a view which have dynamic number of colums based on the select response? 2) Is it possible to name view colums based on some data retrieved with select statement? My pseudo definition, which was already simplified, could be simlified more. Maybe I do that and try crosstab for starters. As I told John, this model is one that works fine in one large application using MS SQL-Server. This view I am trying to create, is something that the application mentioned doesn't include. The model itself has been proven to be all working and extremely dynamical. This is what I discussed with John outside the list, forgive me, I didn't notice that, neither did John I guess.. - mika - ----------------------------------------------------------------------------------------------------------------------------------------------------- John, dynamic structures. Even if they were only one and the administrator user changed one, it would have some challange on it. Actually I have built my app on top of Apache Cocoon, so using XML, as Cocoon utilizes SAX stream and everything is already in XML syntax, wouldn't be a bad option. But this question of mine is considering a part where the consumer is a GIS-Server which don't know anything about XML datastores. PostGIS extension on Postgresql makes it a suitable datastore. - mika - On Tue, 10 Apr 2012 02:37:39 -0700, John R Pierce <pierce@hogranch.com> wrote: On 04/10/12 2:24 AM, mika@digikartta.net wrote: > > Let's say I have tens or hundreds or thousands of feature (instance) > types. Each of them would need its own table. > Let's say I give for the application administrator, a possibility to > create new feature types? He/she merely knows what's the database is. > So I need a model which is capable for offering "dynamic table > structures". you have 1000s of different data structures (classes) in your application? really? it sounds to me like you're describing a key-value store. these have no ready method of implementing data integrity, and make a very poor fit to the relational model, resulting in very cumbersome queries that don't execute efficiently. you'll welcomed with open arms by the NoSQL community, however. store everything as XML and go to town with any number of cloudy storage systems. 10.4.2012 16:13, Merlin Moncure kirjoitti: > On Tue, Apr 10, 2012 at 4:27 AM,<mika@digikartta.net> wrote: >> Let's say I have tens or hundreds or thousands of feature (instance) types. >> Each of them would need its own table. >> Let's say I give for the application administrator, a possibility to create >> new feature types? He/she merely knows what's the database is. >> So I need a model which is capable for offering "dynamic table structures". >> >> I already have implemented parts which allow creating these types, creating >> instances of them and populating those instances with data. Now I have to >> come up with a method for flattening that data into one view so that it can >> be used directly. > If you want a schemaless design in a relational database, you have a > some options: EAV model, hstore, xml (soon json too) as data. I > consider these to be niche solutions not well suited to general > purpose data management. For the most part, SQL really only works > properly with a well defined schema. > > Your incoming data looks to be EAV-ish. You can build horizontal > structures with crosstab and what you're trying to do looks semi > doable, but it's going to to be quite difficult. > > merlin
On Tue, Apr 10, 2012 at 9:31 AM, Mika M Lehtonen <mika@digikartta.net> wrote: > Merlin, > thanks for your reply. Semidoable? Let's break this into parts: > 1) Is it possible to create a view which have dynamic number of colums based > on the select response? Mostly no. A hardwired restriction is that a view has a rigidly defined list of columns with defined types. You can skirt that restriction a couple of ways -- for example your view could be a single column text (or xml, or hstore) with the columns you want encoded into it. > 2) Is it possible to name view colums based on some data retrieved with > select statement? nope -- in fact the point of views is to create a well defined representation of something which is the opposite of what you are trying to do. > My pseudo definition, which was already simplified, could be simlified more. > Maybe I do that and try crosstab for starters. As I told John, this model is > one that works fine in one large application using MS SQL-Server. This view > I am trying to create, is something that the application mentioned doesn't > include. The model itself has been proven to be all working and extremely > dynamical. > > This is what I discussed with John outside the list, forgive me, I didn't > notice that, neither did John I guess.. merlin
On Tue, Apr 10, 2012 at 09:43:52AM -0500, Merlin Moncure wrote: > Mostly no. A hardwired restriction is that a view has a rigidly > defined list of columns with defined types. You can skirt that > restriction a couple of ways -- for example your view could be a > single column text (or xml, or hstore) with the columns you want > encoded into it. I'm wondering whether a set-returning (or these days, I guess, table-returning) function or a polymorphic function might make sense here. I haven't read the use case carefully (and I probably won't), but it seems like it might not be impossible that way. Best, A -- Andrew Sullivan ajs@crankycanuck.ca