Thread: Recommended Modeling Tools?

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

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...

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