Online Documentation for SQL Manager for PostgreSQL

Setting table properties


Use the Table tab of Table Editor to create a table and specify its properties.

 

Table name

Select a schema and enter a name for the new table. Note that table names must comply with the rules for identifiers and must be unique within the schema. A table name can contain a maximum of 128 characters.

 

Table type

RadioButton Ordinary table

Use this option to create an ordinary PosgreSQL table.

 

RadioButton Foreign table

Select this option to create a foreign table. Such tables allow data stored outside the database to be used like native PostgreSQL-stored data. From the drop-down list select the foreign server to be used for the foreign table.

If the selected foreign server has the 'postgres_fdw' as a data wrapper then the Select Table button becomes available.

 

RadioButton Temporary table

Select this option to create a temporary table. A temporary table will automatically be deleted if the connection dies and the name is valid per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name (the existing table is hidden until the temporary table is deleted).

 

Behavior on end of transaction

This group allows you to define the temporary table behavior at the end of a transaction block:

RadioButton Preserve rows

RadioButton Delete rows

RadioButton Drop table

 

New table - Setting table properties

 

 

Table owner

Use the drop-down list to select the user that will own the table being created.

 

Tablespace

Use the drop-down list to specify the new table data storage (a tablespace).

 

Options

CheckBox With OIDs

This option specifies whether rows of the new table should have OIDs (object identifiers) assigned to them.

 

CheckBox Unlogged

For PostgreSQL ver. 9.1 and above.

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables.

Note: An unlogged table is automatically truncated after a crash or unclean shutdown.

 

Fillfactor

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.

 

CheckBox Use default fillfactor.

Check this option to set fillfactor value to 100.

 

Fillfactor

Specify the custom fillfactor value in this field.

Note: To set this value you need to uncheck CheckBox Use default fillfactor option.

 

Inherits from

This area allows you to define the table(s) to inherit properties from. Use of inheritance creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s).

To select a table, you need to move it from the Available Tables list to the Selected Tables list. Use the Move to Selected Move to Available buttons or drag-and-drop operations to move the tables from one list to another.

 

The following icons distinguish inherited tables in Database Explorer:

iconHasInheritedTable - means that there is a table inherited from this table

iconIsInherited - means that the table is inherited from another one

iconIsInheritedandHasInherited - means that the table is inherited from a table and there is another table inherited from this table

 

To compile the table, use the iconCompile Compile item available within the Navigation bar.