Tutorial 9 - Edit tables content

This tutorial show, how to edit database table content using KPoGre.

Open table editor - the Datatable

Select table you wish to edit in tree, make right mouse click to display menu and choose Edit table content. Table must have primary key.

KPoGre shows dialog, you may enter WHERE predicate to limit anmount of edited rows and ORDER BY predicate to specify sort orded of edited data. It is good practice specify at least one sort column, because updated rows are returned on end of result when you reload data and sort order is not specified.

KPoGre shows Datatable with table content. Also toolbar is displaied.

Datatable menu and toolbar actions

When Datatable is opened, you may use items in Database submenu from main menu bar. KPoGre also shows toolbar with actions for it. Aditionally, you may reach those actions by make right click over datatable to show menu. On this menu, there are some useful clibpoard actions.

Moving current row

There are actions for navigate to First row, Previous row, Next row and Last row. This same effect have navigate keys on keyboard - Up, Down, Home and End keys. Current row can be also changed using mouse by selecting it.

Edit data

When cell is selected, you may start edit it's content using Edit cell action, or by pressing F2, or simply by start typing on keyboard. Current cell content can be cleared by Clear cell or pressing Delete. Cell editing can be canceled by pressing Esc.

Insert new row using action Insert row or pressing Ctrl+Insert. New row is inserted at end of table and it's ready to fill data. User must fill all fields, except NULLable or with DEFAULT value. When table have primary key autogenerated from sequence, leave it's cell blank, Datatable select next sequence value and use it. After fill row, use Update changes or presses Ctrl+Enter to perform INSERT operation.

Delete current row using action Delete row or pressing Ctrl+Delete.

Commit or cancel changes

Datatable is row oriented editor. When a user makes changes to a cell in the table, current row's data are stored to backup buffer and cell is marked for update. The Datatable will not send changes in the current row to the database until the user moves to a different record in the datatable or use Update changes or presses Ctrl+Enter.

Row editing or inserting can be canceled using Cancel changes, or by pressing Esc. In this case, data are restored from backup buffer or insert is cancelled.

Reload data

Using action Reload data user can perform SQL query to actual data in database table. KPoGre displays dialog to give chance to modify WHERE or ORDER BY clauses.

Using action Stop reload data user can interrupt this operation.

Clipboard actions

When user make right mouse click over Datatable, KPoGre shows popup menu, where are some useful actions for copy current cell, row or entire table to clipboard.

Content of Datatable may be saved to CSV file using File -> Export result.

Data conversions

Only integer, floating-point and boolean types KPoGre convert from text form before sending it to database. If this conversion fail, error dialog is displayied.

Other types are sent in textual form, entered by user. PostgreSQL makes conversion by it's internal functions. When error during INSERT or UPDATE operation occured, error dialog is displayied.

Numeric types

Integer and floating-point types are converted by KPoGre. If conversion fail, check your locale setting.

Arbitrary precision numbers (numeric and decimal) and money are sent to database in textual form. Money can be entered without currency symbol.

Boolean type

Following text are converted to true : t, T, y, Y, 1.
Following text are converted to false : f, F, n, N, 0.
Only first entered character is significant.

Date and Time types

Date types are sent to database in textual form entered by user. PostgreSQL makes conversion by it's internal functions.

Date input value samples

Input String	Description
25.1.2006	Date in DD.MM.YYYY mode
1/18/1999	January 18 in MM/DD/YYYY mode
January 8, 1999	Unambiguous in any datestyle input mode

`

Time input value samples

Input String	Description
17:52:00	Time
17:52:03.658	Time with miliseconds
04:05:06 PST	Time with specified time zone by name
15:12:06 CET	Time with specified time zone by name
15:12:06+01	Time with specified time zone by value

Interval input value samples

Input String	Description
1h	1 hour interval, 01:00:00
2 years	2 years interval
1 12:59:10	Unambiguous, can be read as: 1 day 12 hours 59 min 10 sec

See PostgreSQL documentation for more informations about input formats for differents Date and Time types.

Bit strings

Bit strings are sent to database in textual form entered by user. PostgreSQL makes conversion by it's internal functions.

Bit string input value samples

Input String	Description
1101010101010101	String of 1's and 0's

See PostgreSQL documentation for more informations about input formats for Bit strings types.

Geometric types

Geometric types are sent to database in textual form entered by user. PostgreSQL makes conversion by it's internal functions.

Geometric input value samples

Input String	Description
(5,6)	point, x: 5, y: 6
[((1, 0),(25,14))]	lseq, x1: 1, y1: 0 x2: 25, y2: 16
((1, 0),(25,14))	polygon, x1: 1, y1: 0 x2: 25, y2: 16
<(5,6), 10>	circle, center x: 5, y: 6 radius: 10

Note: Datatype line is not fully implemented yet in PostgreSQL 8.1.4. It can be used when CREATE TABLE, but can't be edited.

See PostgreSQL documentation for more informations about input formats for Geometric types.

Network address types

Network address types are sent to database in textual form entered by user. PostgreSQL makes conversion by it's internal functions.

Network address input value samples

Input String	Description
192.168.100.128/25	cidr - IPv4 and IPv6 networks
192.168.100.128	inet - IPv4 and IPv6 hosts address
08:00:2b:01:02:03	MAC address

See PostgreSQL documentation for more informations about input formats for Network address types.

Large objects

If table contain large object column (text, xml or bytea), its content is stripped to reasonable size for display it. Those fields cannot be edited by inline editor. Instead, press F2 or make double click on cell and KPoGre display popup menu.

Editing Texts

Text fields can be loaded from disk file, saved to it, ...

... or you can launch dialog for edit it.

Editing XML

XML fields may be edited like as texts in KatePart based editor with syntax highlighting.

User can validate XML content using button. When XML is not well formed, error dialog is displaied, and cursor is placed to error position in editor.

Editing Byte arrays

Byte array fields can be loaded from disk file or saved to it.

Encoding notes

When you save large object field to file, it's encoded with this same encoding, that have connection to PostgreSQL server, when you load from file, expected encoding is also the same as connection have. Select server in left tree to check wich encoding is used for connection: