Обсуждение: Recommended Modeling Tools?
Can anybody advise on some of the best PostgreSQL modeling tools in terms of keeping the most current with PostgreSQL releases for physical modeling?
For example, I'd like to take advantage of some of the newer partitioning features in PG11, but many of the modeling tools I've investigated don't seem to support 11.x yet (Toad - 10, PGModeler - 10+, Idera ER/Studio - 9.x).
I don't mind writing the initial DDL manually to define the objects, but I'd like a tool which can properly reverse engineer schemas into SQL scripts.
Thanks in advance for any recommendations!
-Matt
On 04/09/2019 05.51, matthewph76 wrote: > Can anybody advise on some of the best PostgreSQL modeling tools in > terms of keeping the most current with PostgreSQL releases for > physical modeling? > > For example, I'd like to take advantage of some of the newer > partitioning features in PG11, but many of the modeling tools I've > investigated don't seem to support 11.x yet (Toad - 10, PGModeler - > 10+, Idera ER/Studio - 9.x). > > I don't mind writing the initial DDL manually to define the objects, > but I'd like a tool which can properly reverse engineer schemas into > SQL scripts. > > Thanks in advance for any recommendations! > > -Matt This is an interesting question indeed. However the lack of responses and the mentioned lack of support for newer PGSQL versions in those tools would suggest there is not much interest in modelling tools in the Postgres community... Reagards, Arni
On 9/6/19 3:15 AM, Arni wrote: > On 04/09/2019 05.51, matthewph76 wrote: >> Can anybody advise on some of the best PostgreSQL modeling tools in >> terms of keeping the most current with PostgreSQL releases for >> physical modeling? >> <snip> >> -Matt > This is an interesting question indeed. However the lack of responses > and the mentioned lack of support for newer PGSQL versions in those > tools would suggest there is not much interest in modelling tools in the > Postgres community... > > Reagards, > Arni pgmodeler: so far I'm a fanboy. More oriented toward semantic DBMS features than physical. pgadmin4 is (now) packaged for OpenSuSE; me being neither packager, nor Python guy, somebody got there before me. Install of the RPM read in one hundred twenty (120) required python packages (libraries bundled for RPM n dependency soliver.). So, I feel less bad about running out of cycles doing that by hand, earlier. 120+ required python libraries suggests pgadmin4 might be a tad brittle in maintenance. Somebody's gonna change something... -- Jim Bullock, Rare Bird Enterprises, "Conscious Development" LinkedIn: http://www.linkedin.com/in/rarebirdenterprises Listen to a round table of experts in these books from Dorset House: Roundtable on Project Management, http://www.dorsethouse.com/books/rpm.html Roundtable on Technical Leadership, http://www.dorsethouse.com/books/rtl.html
Hi,
try Navicat
https://navicat.com/en/products/navicat-for-postgresql
Modelling tool is included in Enterprise edition. Win, Mac and Linux.
https://navicat.com/en/products/navicat-for-postgresql-feature-matrix
I'm using Navicat Enterprise on Postgre 9.6 to 11.5. Have worked very well for me.
Thanks,
Morten
Den 06.09.2019 21.10, skrev jbullock:
On 9/6/19 3:15 AM, Arni wrote:On 04/09/2019 05.51, matthewph76 wrote:Can anybody advise on some of the best PostgreSQL modeling tools in terms of keeping the most current with PostgreSQL releases for physical modeling?<snip>-MattThis is an interesting question indeed. However the lack of responses and the mentioned lack of support for newer PGSQL versions in those tools would suggest there is not much interest in modelling tools in the Postgres community... Reagards, Arnipgmodeler: so far I'm a fanboy. More oriented toward semantic DBMS features than physical. pgadmin4 is (now) packaged for OpenSuSE; me being neither packager, nor Python guy, somebody got there before me. Install of the RPM read in one hundred twenty (120) required python packages (libraries bundled for RPM n dependency soliver.). So, I feel less bad about running out of cycles doing that by hand, earlier. 120+ required python libraries suggests pgadmin4 might be a tad brittle in maintenance. Somebody's gonna change something...
Hi all, I have a query which works in MySQL but not in PostgreSQL and I would be very grateful to receive an explanation as to why. The scenario is this. I have records like this (fiddles for MySQL and PG given at bottom) CREATE TABLE tab ( t_id SERIAL NOT NULL PRIMARY KEY, t_key INTEGER NOT NULL, t_name VARCHAR(10) NOT NULL, t_value VARCHAR(10) NOT NULL ); INSERT INTO tab (t_key, t_name, t_value) VALUES (75, 'Couleur', 'Bleu'), (75, 'Taille', 'Grand'), (75, 'Poids', '20'), (75, 'Teint', 'Y'), (76, 'Couleur', 'Bleu'), (76, 'Taille', 'Grand'), (76, 'Poids', '20'), (76, 'Teint', 'Y'), (77, 'Couleur', 'Bleu'), (77, 'Taille', 'Grand'), (77, 'Poids', '20'), (77, 'Teint', 'N'); Now, I want to be able to 75 and 76 as matching because they match on all values of both t_name and t_value. 77 doesn't match because Teint is 'N' whereas for the others it's 'Y'. OK, so, I have the following query (which works for MySQL 5.7 and 8.0.17 (but not 5.6 strangely). SELECT DISTINCT LEAST(t1key, t2key) AS "lst", GREATEST(t1key, t2key) AS "gst", COUNT(LEAST(t1key, t2key)) AS "mn_c" -- COUNT(GREATEST(t1key, t2key)) AS mx_c FROM ( SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value", t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value" FROM tab t1 JOIN tab t2 ON t1.t_key != t2.t_key AND t1.t_name = t2.t_name AND t1.t_value = t2.t_value ORDER BY t1.t_id, t2.t_id ) AS t1 GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key) HAVING COUNT(LEAST(t1key, t2key))/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key) ORDER BY 1, 2, 3; Now, in MySQL this gives lst gst mn_c 75 76 8 but in PG, I get the following error ERROR: subquery uses ungrouped column "t1.t1key" from outer query LINE 20: ...)/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key) ^ The PG fiddle is here https://dbfiddle.uk/?rdbms=postgres_10&fiddle=51d80aa3ce4e82cf18691eea7c7a1075 and the MySQL one is here https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=50ba15f39909c98958bceb2a79b36ac7 I have fiddled around (pardon the put 8-) ) but can't seem to get this to work. I would be grateful for a) a working query in PG and more especially b) an explanation of what's going on and why the MySQL query (which appers valid to me) won't work for PG. Now, I'm fully aware that PG is **WAY** more standards compliant than MySQL, but this one has me baffled. I know that I could probably introduce another level of outer query to get the result I require but that strikes me as inelegant. I'm probably missing some fundamental part of set theory and relational algebra. Any references, URLS, other sources that would explain this to me would be gratefully received. TIA and rgs, Pól...
=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.ie> writes: > ... I have the following > query (which works for MySQL 5.7 and 8.0.17 (but not 5.6 strangely). > SELECT > ... > GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key) > HAVING COUNT(LEAST(t1key, t2key))/2 = (SELECT COUNT(tab.t_key) FROM > tab WHERE t_key = t1key) > but in PG, I get the following error > ERROR: subquery uses ungrouped column "t1.t1key" from outer query > LINE 20: ...)/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key) Well, it's right: the sub-select refers directly to t1key from the outer query, and t1key does not have a well-defined value in the HAVING clause. As an example, if you had a row with t1key=1 and t2key=2, and another row with t1key=2 and t2key=1, those would fall into the same group, because the LEAST and GREATEST values will be 1 and 2 respectively for both rows. So which value of t1key would you expect the HAVING clause to use? MySQL is rather infamous for not worrying too much about whether queries like this have any well-defined result, so the fact that it fails to throw an error is sad but not very surprising. You got back some answer, but who knows which value of t1key they used? It's not very clear to me what you're really trying to do here, and in particular I don't follow why grouping by the LEAST and GREATEST values is appropriate, so I don't have any solid advice on what you ought to do to fix the query. Maybe the GROUP BY clause should just be "GROUP BY t1key, t2key"? regards, tom lane
Hi Tom (+ group), and thanks for gettng back to me, > Maybe the GROUP BY > clause should just be "GROUP BY t1key, t2key"? No "maybe" about it Tom - I continued working on it and the query I finally came up with (which now works perfectly for both PG and MySQL) SELECT DISTINCT LEAST(t1key, t2key) AS "lst", GREATEST(t1key, t2key) AS "gst", COUNT(LEAST(t1key, t2key)) AS "mn_c" -- << NOT NECESSARY - SHOWS NO. OF DUPS -- COUNT(GREATEST(t1key, t2key)) AS mx_c FROM ( SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value", t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value" FROM tab t1 JOIN tab t2 ON t1.t_key != t2.t_key AND t1.t_name = t2.t_name AND t1.t_value = t2.t_value ORDER BY t1.t_id, t2.t_id ) AS t1 GROUP BY t1.t1key, t1.t2key --- <<<< Exactly as you suggested HAVING COUNT(LEAST(t1key, t2key)) -- <<<< = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t1key) AND COUNT(GREATEST(t1key, t2key)) = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t2key) ORDER BY 1, 2; And now the two "reciprocal" HAVING clauses pick out the required records perfectly. I can sort of see the PG philiosphy of being stricter - the ONLY_FULL_GROUP_BY fiasco springs to mind. The PG fiddle https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e48caa900335a27e390a5394f4faef28 and MySQL one https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b89903cb96d44c145b48a8e5172f289 It shows exactly the result desired - sets of records grouped by t_key which are identical in both t_name and t_value are chosen gst mn_c 75 76 4 75 78 4 76 78 4 85 86 3 92 93 2 94 95 1 So 75 is identical to 76 and 78. 85 is identical to 86 and so on. The beauty of having chosen to test with PostgreSQL is that if I hadn't done it, my original semi-working MySQL solution could have failed under production conditions (sorry not could, would have failed). Again proving the (virtually) infinite superiority of PostgreSQL over MySQL. Thanks again and rgs, Pól... > regards, tom lane