Skip to main content

NineData Fully Supports PostgreSQL Visual Table Structure Design

"PostgreSQL is the open-source relational database most similar to Oracle," and because of this, many enterprises favor PostgreSQL, using it as a substitute for Oracle. Undoubtedly, PostgreSQL is very common in enterprises at present.

For developers who directly interact with PostgreSQL, even veterans who have been in the industry for many years may find it troublesome to create tables in the black screen of PostgreSQL. There are many syntaxes and parameters to remember, and it is easy to make mistakes such as spelling errors and punctuation errors, leading to various errors. Especially when facing complex table structures, writing SQL statements on the black screen can be frustrating. Apart from creating tables, modifying table structures is even more maddening. The modification process cannot have any errors, otherwise, it may even affect the business.

Therefore, it is very important to replace the black screen interface with a fully functional graphical tool. NineData fits the bill perfectly. The goal of NineData is to enable anyone, regardless of their understanding of PostgreSQL, even newcomers, to quickly design PostgreSQL table structures.

What are the highlights?​

In the last iteration, NineData officially released the visual table structure design feature for PostgreSQL. To meet the needs of developers for graphical table structure design in PostgreSQL, NineData's approach is as follows:

  • Modularization: Categorize the complex parameters of the table creation statement and summarize them into five modules: Columns, Indexes, Foreign Keys, Partitions, and Table Options. The interface layout is very clear, and the learning cost is basically 0.
  • Comprehensive Parameter Coverage: For parameters of various attributes such as tables, fields, and constraints, complete definitions are provided, including the creation of partition attributes and partitioned tables, various constraints and ordinary indexes, field data types, and verification set generation column attributes, etc.
  • Simple Operation: Provide convenient interactive operations, combined with real-time SQL preview to help users quickly complete table structure definition and editing.

The goal of NineData can be summarized in one sentence: veterans can increase efficiency, and newcomers can get started quickly.

How to visually create PostgreSQL table structures?​

Through NineData's SQL Console, you can visually create PostgreSQL tables, covering almost all table creation statement capabilities.

  • Configure Columns: Includes basic configurations such as column name, data type, NOT NULL attribute, default value, comments, Primary Key attribute, etc., and also supports generated columns, COLLATE (column sorting rules), etc.

    image-20240715172047670

    In the above figure, 5 columns are configured, with ID set as the primary key and not null, and full_name as a generated column, the value of which is the combination of the first_name and last_name columns.

  • Configure Constraints: Provides primary key indexes (PRIMARY KEY), unique indexes (UNIQUE), exclusive indexes (EXCLUSION), CHECK, and ordinary indexes (INDEX).

    image-20240715172116629

    The above figure sets a CHECK constraint for the age column, and the input age must be greater than 18.

  • Configure Foreign Keys: Supports configuring foreign keys for the current table, referencing the primary key column of another table, and supports creating one or more foreign keys.

    image-20240715172133503

  • Configure Partitioned Tables: Visually create partitioned tables for the current table, bringing performance improvements to the database in terms of query performance.

    image-20240715172150695

    In the above figure, specify the age range of the age column for partitioning in Partition By, and then enter the specific partition statement in Partitions.

  • Table Options: Configure the owner (Owner) and tablespace (Tablespace) for the table, and you can also copy the structure and data of an existing table through the LIKE...INCLUDING attribute. It also supports entering other table options, such as INHERITS, WITH/WITHOUT OIDS, ON COMMIT, etc.

    image-20240715172240294

  • Preview SQL: After the configuration is completed, click Save on the page, NineData will convert all configurations into specific SQL, and after confirming that there are no errors, click OK.

    image-20240715175537747

    In the SQL Console, you can see that the table has been created.

    image-20240715175757221

Summary​

As you can see, with just a few clicks of the mouse, a table for PostgreSQL has been successfully created. Since NineData automatically generates SQL statements based on your choices on the interface, it basically says goodbye to table creation failures caused by low errors and missing parameters, and I believe it can improve your work efficiency to a certain extent.

Last updated on