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?​

Use NineData SQL Console to visually create PostgreSQL tables and cover most 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. Copy the structure and data of an existing table through the LIKE...INCLUDING attribute. Other table options, such as INHERITS, WITH/WITHOUT OIDS, and ON COMMIT, are also supported.

    image-20240715172240294

  • Preview SQL: After configuration finishes, click Save on the page. NineData converts all configurations into SQL. Confirm that the SQL is correct, then click OK.

    image-20240715175537747

    In SQL Console, the new table appears.

    image-20240715175757221

Summary​

NineData creates the PostgreSQL table from the visual configuration and generates the corresponding SQL statements. This reduces table-creation failures caused by manual SQL errors or missing parameters.