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.

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.

Anonymous

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?

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.

Nathan_Clayton

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

Roenbaeck

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.