Appendix IV: FAQ and troubleshooting
Short answers to common questions, with a link to the page that explains each subject in full. If you did not find yours, look at the glossary for the meaning of a term, or ask at the GitHub issues page.
The questions are grouped by subject:
- Editions and installation
- Files, settings and teamwork
- Relationships and generated objects
- Working on the model
- Data modeling advice
- Validation and recovery
- Export and connections
- Import, migration and support
1. Editions and installation
Do I need a PostgreSQL server to use pgModeler?
No, to model. You can create a model, validate it, see the SQL, and export it to a SQL file, to images (PNG and SVG) or to a data dictionary without any server. A server is needed to export the model directly to a database, and by the tools that work with live databases (import, diff and the Manage view, which are Plus features). The compilation also needs the PostgreSQL client library (libpq): see Installation.
What is the difference between pgModeler Community and Plus?
Community is free and open source and has the whole modeling experience. Plus adds the tools that work with a live server (database import, model and database comparison, the Manage view with the SQL tool) and a set of plug-ins. The complete list is in Community and Plus editions.
The Import, Diff or Manage views are missing or disabled. Why?
They are Plus features. In Community those views are not available. The same applies to the import and the comparison in the command-line interface; the export to a database server, in the graphical interface as well as in the command-line interface, is available in both editions.
Which PostgreSQL versions are supported?
The objects and fields of the editing forms follow PostgreSQL 10.x to 18.x. Fields that exist only in some versions are marked as version specific (see Version specific fields), and the SQL generated for each object takes the version you choose when exporting into account.
2. Files, settings and teamwork
Where does pgModeler store my settings? And my models?
Settings are stored per user, in C:\Users\[user]\AppData\Local\pgmodeler-[version] on Windows, /home/[user]/.config/pgmodeler-[version] on Linux and /Users/[user]/Library/Preferences/io.pgmodeler-[version] on macOS (see Handling settings). Models are ordinary files, with the extension .dbm, saved wherever you choose, so they can be copied, backed up and put under version control like any source file.
Can I work on the same model with other people?
A model is a single file, so the usual way is to keep it in a version control system. Plus includes a versioning plug-in that works with Git, and the split model format, which saves the XML of each object in its own file, so the changes made to each object are easier to follow when a team handles the model.
Can I generate the SQL or the images without opening the interface?
Yes, with the command-line interface, which exports models to SQL, PNG, SVG and data dictionaries, fixes model files and, in Plus, imports and compares, and can be used in scripts and pipelines.
3. Relationships and generated objects
Why can't I edit a column or constraint that is green and in italics?
It was created by a relationship, which owns it (this is the column propagation), so that the foreign key follows the relationship when you change it. Right-click the object and choose Open relationship to edit the relationship that owns it. If you really need to edit the column or the constraint by hand, convert the relationship: it becomes a foreign key relationship and the objects are unlocked. See Managing relationship generated objects.
Should I link tables with relationships or create the foreign keys myself?
Use relationships: they create the columns and constraints for you, keep them in sync and draw the line on the canvas. Creating the foreign keys as constraints of the tables is possible (pgModeler then draws the relationship that represents each one) but it is meant for advanced users who want full control. See Relationships.
How do I model a many-to-many relationship?
Create an n:n relationship between the two tables. pgModeler generates the intermediate table with the two foreign keys when the SQL is created. If you want to name the table, add columns to it or change its keys, convert the relationship: the intermediate table becomes a real table of the model, linked to the two tables by one-to-many relationships.
How do I create a self-relationship (a table related to itself)?
Use it for hierarchies (an employee and their manager, a category and its subcategories) or for items related to others of the same kind. The table needs a primary key, as in any relationship that generates a foreign key.
- Select the relationship tool (menu New > Relationship, or the relationship button of the design view) and the type you need, usually one-to-many (
1:n). - Hold
Shiftand click the table. TheShiftkey is what tells pgModeler that both ends are the same table; without it, the tool waits for a second table. - A line leaves the table and comes back to it, and the foreign key column appears in the same table, in green and italics, like any column generated by a relationship.
For a hierarchy, do not mark the "one" side (the parent) as Required in the relationship form. Otherwise the foreign key is NOT NULL, and the first row of the table, which has no parent, could never be inserted. Leaving it optional makes the column accept nulls: the rows without a parent are the roots of the hierarchy.
A many-to-many self-relationship (n:n) is also possible: pgModeler generates the intermediate table with two foreign keys to the same table (for example, products and the products that compose them). The names generated for the two columns are alike, so adjust them in the Name patterns of the relationship form. See Self-relationship.
What does "Required" mean in a relationship?
It sets the cardinality: whether a row of that table must have a counterpart. For example, a book that must have an author makes the foreign key column of book NOT NULL; if the author is optional, the column accepts nulls. The options of each relationship type and how they change the generated objects are in Relationships.
4. Working on the model
serial or identity column?
Use identity columns in modern versions of PostgreSQL (identity columns exist since PostgreSQL 10, so they are available in every version that pgModeler supports). Both create an auto-increment column, but an identity column (GENERATED ... AS IDENTITY) is the SQL standard form, in which the sequence belongs to the column and is handled together with it (it is removed together with the column, and no privilege on the sequence is needed to insert rows). serial is an older convenience of PostgreSQL that creates a separate sequence and sets the default value of the column. In the column form you pick the identity type in the field Identity: ALWAYS does not accept values typed by the user, and BY DEFAULT does.
serial is still found in many existing databases, and a model that comes from one will have serial columns. If you want to see or change the sequence behind one of them, pgModeler can convert a serial to a sequence and back.
How do I organize a large model?
- Put the objects in schemas, as you would in the database.
- Use layers to show only part of the model at a time.
- Use multi-scene models, which spread the objects over several canvases.
- Color-code tables with tags, and find objects with the object search.
The model is slow or the canvas is heavy.
Large models take more time to draw. Some things that help:
- In the general settings, check Disable render smoothness, which turns off the antialiasing of lines and texts and improves the performance with huge models, and uncheck Use curved lines for relationships, since straight lines are cheaper to draw.
- Hide the relationships with the Hide relationships toggle button of the scene information bar (below the canvas). The relationship lines are replaced by a relationship marker in each table, which is lighter to draw and keeps the details one double-click away. According to the manual, the change takes effect after the model is saved and reloaded, and pgModeler itself suggests the option when the model has 2000 or more relationships. See Multi-scene models.
- Use layers or multi-scene to work with part of the model at a time.
- Collapse the attributes of tables (see attributes pagination and collapsing) and use the compact view.
I only need to add a few objects to a database that already exists. How do I avoid exporting everything?
Create the objects that already exist in the database and check Disable SQL code on them: they become stub objects, which serve as references for the new objects but are not exported. See Creating stub objects.
How do I stop an object from being changed by accident?
Protect it: protected objects cannot be edited, moved or deleted until you unprotect them.
Can I undo a change?
Yes, with Ctrl + Z (and Ctrl + Y to redo). The operations history lists what was done to the model.
5. Data modeling advice
These answers are general advice, not rules of pgModeler.
Surrogate key or natural key?
A surrogate key (an identity column) never changes and is small, which makes foreign keys cheap and stable. A natural key (an e-mail, a country code) carries meaning but can change or turn out not to be unique. A common practice is to use a surrogate primary key and also create a unique constraint on the natural key, so the rule of the business is still enforced by the database.
Should I index the foreign key columns?
PostgreSQL does not create an index on the referencing side of a foreign key. Without one, deleting or updating the referenced row, and joins on the key, need to scan the whole table. The relationships settings include the index type that pgModeler uses for an index on the foreign key columns generated by relationships; check the SQL of your tables to confirm that the index exists, or create it yourself in the Indexes tab of the table.
varchar(n) or text?
In PostgreSQL they perform the same. Use varchar(n) when the limit is a real rule of the data (a code of 3 characters, for instance) and text when it is not.
Which type should I use for dates, money and identifiers?
For moments in time prefer timestamptz (which stores the instant and shows it in the time zone of the session) over timestamp. For money use numeric with the scale you need, not float or the money type. For identifiers coming from other systems (UUIDs, for example) use the specific type (uuid) instead of text.
Enum type or lookup table?
An enum is simple and compact for a short list that rarely changes. A lookup table (with a foreign key to it) is better when the list changes often, when each value needs more attributes (a description, an order) or when other systems also read it.
How should I name things?
Pick one convention and keep it everywhere. A frequent one is lower case with underscores (snake_case), tables in the singular or the plural (but not mixed) and foreign key columns named after the referenced table (author_id). Avoid upper case and spaces: they force you to quote the names in every query. pgModeler generates the names of the objects created by relationships from patterns that you can change in the relationships settings.
When should I use inheritance or partitioning?
Most models do not need either. Partitioning is for very large tables that are queried by a range or a list of values (a date, a region). Inheritance is rarely the best way to represent a generalization in a relational design. Both are available as relationship types: see Relationships.
Where can I find more modeling advice?
The PostgreSQL community keeps a wiki page, Don't Do This, that lists common mistakes in the use of PostgreSQL (data types and constructs that look fine but cause problems later), with the reason for each one and what to do instead. It is a good checklist to read before finishing a model. For the full description of each type and command, the official PostgreSQL documentation is the reference, and this manual links to it in the pages of each object.
6. Validation and recovery
The validation reports errors that I do not understand. What do I do?
Each error says what is wrong. The most common one is a reference break: an object was created before something it depends on. Click Apply fixes and pgModeler reorders the objects. Warnings (for example, a unique key with the same columns as the primary key, or a foreign key with SET NULL on a NOT NULL column) are accepted by PostgreSQL but probably do not do what you expect, and have to be fixed by hand. See Model validation.
The validation says that the PostGIS extension is missing.
A column uses a type of PostGIS (geometry, geography...) and the model has no extension named postgis. Create the extension in the model, or let the automatic fix create it in the schema public.
pgModeler says that a model file is corrupted and cannot load it.
It usually happens when the model was saved with unresolved validation errors, or comes from an older version with incompatibilities. Use the model fix tool (the Fix view, or the command-line interface) to repair the file, and always validate before saving.
pgModeler crashed or closed unexpectedly. Did I lose my work?
Probably not. On the next start pgModeler offers to restore the models that were open; save them right away (File > Save All). If it was a crash, the crash handler generates a report with technical details that helps the developers to find and fix the problem.
7. Export and connections
The export to the server failed.
Read the Output tab of the export dialog: it shows the command that PostgreSQL rejected and its message. The usual causes are lacking privileges in the connection (creating a database needs a role with that permission), an object that already exists, and objects that were not created in a valid order (validate the model first). Remember that exporting over an existing database may destroy data: make a backup, or use the diff, which only changes what differs. See Export a model.
The SQL file generated by pgModeler fails in psql.
Check that the model validates, that the PostgreSQL version selected for the export is the one of the server, and that no custom SQL or default value of the model is invalid (the validation cannot detect problems in these). The source code preview shows the SQL of any object so you can find the one that fails.
pgModeler cannot connect to my server.
Check the connection (host, port, user, database and SSL mode) and that the server accepts it: listen_addresses and pg_hba.conf on the server side, and any firewall in between. If the server is reachable only through SSH, Plus has the SSH tunnel plug-in.
8. Import, migration and support
The database import (reverse engineering) fails or the model is incomplete.
The import reads the system catalogs of the server and can fail on unusual structures. In the import dialog you can select only the objects you need, check Ignore import errors to continue after a failure, and use Debug mode, which prints the catalog queries and the code of the created objects in the Debug tab. Models imported with the trial license cannot be saved. See Import a database.
My database is in MySQL, SQLite, SQL Server or Oracle. How do I bring it to pgModeler?
Convert it to PostgreSQL first, with tools such as pgloader or ora2pg, and then import the result. See Migrating from other tools.
Where do I report a bug or suggest an improvement?
At the GitHub issues page, with as many details as possible (version, operating system and the steps to reproduce). Mistakes in this manual can be reported there too.