Improving Wetware

Because technology is never the issue

PostgreSQL version of ERD Query for GraphViz

Posted by Pete McBreen 31 Oct 2016 at 19:42

Finally got around to doing the PostgreSQL version of the ERD generator using GraphViz. This uses the database from the PostgreSQL Tutorial site

As before there is a PostgreSQLERDGeneration.sql query, the resulting DVDRental.gv dot file for graphviz to generate the file and the resulting DVDRental.html. Inline interactive version of the diagram is below.

A few differences

  • Primary keys are sorted to the top of the table symbols
  • Lines are thicker on hover to make it easier to select the relevant symbol
  • Query does not filter out empty tables.

This completes the set of databases that I have made this work for, might include DB2 at some point in the future if I ever work on an IBM system.

For this interactive version, hovering over the lines makes them larger so that you can click to highlight the line. This makes it easy to plan out a query by following the links between the relevant tables, regardless of where they are on the screen. A good example of this would be tracing out which language DVDs are rented out in a specified city? This needs seven tables and six relationships to determine this, and it is much easier to have the path highlighted while writing the query than having to remember the path as you write the query.

dvdrental dvdrental.actor dvdrental.actor actor_id first_name last_name last_update dvdrental.film_actor dvdrental.film_actor actor_id film_id actor_id film_id last_update dvdrental.actor:actor_pkey->dvdrental.film_actor:film_actor_actor_id_fkey dvdrental.address dvdrental.address address_id city_id address address2 district postal_code phone last_update dvdrental.customer dvdrental.customer customer_id address_id store_id first_name last_name email activebool create_date last_update active dvdrental.address:address_pkey->dvdrental.customer:customer_address_id_fkey dvdrental.staff dvdrental.staff staff_id address_id first_name last_name email store_id active username password last_update picture dvdrental.address:address_pkey->dvdrental.staff:staff_address_id_fkey dvdrental.store dvdrental.store store_id address_id manager_staff_id last_update dvdrental.address:address_pkey->dvdrental.store:store_address_id_fkey dvdrental.category dvdrental.category category_id name last_update dvdrental.film_category dvdrental.film_category film_id category_id category_id film_id last_update dvdrental.category:category_pkey->dvdrental.film_category:film_category_category_id_fkey dvdrental.city dvdrental.city city_id country_id city last_update dvdrental.city:city_pkey->dvdrental.address:fk_address_city dvdrental.country dvdrental.country country_id country last_update dvdrental.country:country_pkey->dvdrental.city:fk_city dvdrental.payment dvdrental.payment payment_id customer_id rental_id staff_id amount payment_date dvdrental.customer:customer_pkey->dvdrental.payment:payment_customer_id_fkey dvdrental.rental dvdrental.rental rental_id customer_id inventory_id staff_id rental_date return_date last_update dvdrental.customer:customer_pkey->dvdrental.rental:rental_customer_id_fkey dvdrental.film dvdrental.film film_id language_id title description release_year rental_duration rental_rate length replacement_cost rating last_update special_features fulltext dvdrental.film:film_pkey->dvdrental.film_actor:film_actor_film_id_fkey dvdrental.film:film_pkey->dvdrental.film_category:film_category_film_id_fkey dvdrental.inventory dvdrental.inventory inventory_id film_id store_id last_update dvdrental.film:film_pkey->dvdrental.inventory:inventory_film_id_fkey dvdrental.inventory:inventory_pkey->dvdrental.rental:rental_inventory_id_fkey dvdrental.language dvdrental.language language_id name last_update dvdrental.language:language_pkey->dvdrental.film:film_language_id_fkey dvdrental.rental:rental_pkey->dvdrental.payment:payment_rental_id_fkey dvdrental.staff:staff_pkey->dvdrental.payment:payment_staff_id_fkey dvdrental.staff:staff_pkey->dvdrental.rental:rental_staff_id_key dvdrental.staff:staff_pkey->dvdrental.store:store_manager_staff_id_fkey