PostgreSQL support

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL support

Nathan Clayton
I'm working on adding PostgreSQL support to the modeler over at https://github.com/nathanclayton/anchormodeler in the nc.postgresql branch.

There's some tweaking to the naming convention that I've needed to do to reduce the amount of code generated (e.g. not have to create a unique trigger function for each checksummed knot), and much of it is built off of the Elari.Postgresql branch with a lot of MS SQL code still in there to be fixed.
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

delostilos
Hi,

As of PostgreSQL 9.3 lateral joins are possible. They are similar to the cross apply of SQL Server/Oracle. You can replace 'cross apply' with 'cross join lateral' and have then the same functionality. This will help in the difference perspective.

Regards,
JJ.
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

Eric Biggs
In reply to this post by Nathan Clayton
I'm curious to know the level of interest for full Postgresql support in the modeler. It appears that there are two efforts, yours and the Elari branch. Have you been in communication about consolidating your efforts? Is this largely out of personal interest or with the ambition of being able to deploy and maintain a critical production data warehouse in Postgresql?
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

Nathan Clayton
Hi Eric,

I'm very interested in using AM to build out a production data warehouse.

I've been in contact with both Elari and Lars regarding this and am building off of Elari's work as well. From our conversations, it appears that Elari is working on the unitemporal modeling, whereas I've been working on the CRT modeling.

Lars has been kind enough to move the SVN repository over to Github here. I've moved my work over to a fork here. I am continuing to bring in the changes made by Elari and Lars as they are made, and once tested I'll submit a pull request to Lars for inclusion into the mainline.
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

Nathan Clayton
In reply to this post by delostilos
Hi JJ,

I'm a big fan of the lateral join feature in 9.3 (no more nested subqueries!) as well as the JSON(B) datatype introduced in 9.2 (it seems to be getting more love than the XML data type, which isn't even enabled on Amazon's RDS).

Nathan
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

roenbaeck
Administrator
In SQL Server we store a copy of the model XML when you execute a generated script. Would it be better to store this as JSON in PostgreSQL?

If so, from the tool, under the Generate menu there is a "JSON" alternative that should generate a description equivalent to the XML. Could anyone of you take a model and generate the JSON and check if PostgreSQL accepts it as valid for storage in a JSON(B) data type? I could easily make both the XML and the JSON descriptions accessible from sisula.
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL support

Nathan Clayton
Hi Lars,
 
It seems like it would indeed be better to store as JSON in Pg. I've been testing a rewrite of the schema views using the JSON functions and the data exported from the Generate menu and it's been working perfectly.
 
As before, I'm having to change some of the column names to avoid quoting, but they're pretty much identical to what's in MS SQL.
 
Nathan
 
 
 
On Sat, Nov 15, 2014, at 02:41, roenbaeck [via Anchor Modeling] wrote:
In SQL Server we store a copy of the model XML when you execute a generated script. Would it be better to store this as JSON in PostgreSQL?
 
If so, from the tool, under the Generate menu there is a "JSON" alternative that should generate a description equivalent to the XML. Could anyone of you take a model and generate the JSON and check if PostgreSQL accepts it as valid for storage in a JSON(B) data type? I could easily make both the XML and the JSON descriptions accessible from sisula.
 


If you reply to this email, your message will be added to the discussion below:
To unsubscribe from PostgreSQL support, click here.