Dump and Migrate PostgreSQL Virtual Generated Columns from a Desktop GUI (PG 18+)
PostgreSQL 18 made virtual generated columns the default. HiDataLinker v1.3.0 keeps VIRTUAL and STORED clauses in Dump and same-engine migrate, and skips those columns in INSERTs.
Since PostgreSQL 18, a generated column is virtual by default: the server computes it when you read the row, and it occupies no storage. HiDataLinker is a free, MIT-licensed, local-first desktop client for PostgreSQL and MySQL. In v1.3.0, Dump and same-engine migrate keep the VIRTUAL or STORED clause in table DDL and omit those columns from INSERT statements — which is what you need from a PostgreSQL 18 generated column GUI if you plan to dump or migrate virtual generated columns to another PostgreSQL 18+ database.
What PostgreSQL 18 changed
The official generated columns documentation describes two kinds:
- Virtual — computed on read, no stored value. This is the default in PostgreSQL 18 and later, including the current PostgreSQL 19 series.
- Stored — computed on write and saved like a normal column. You must write
STOREDto keep this older behavior.
You can make the choice explicit:
CREATE TABLE order_lines (
sku text NOT NULL,
qty numeric NOT NULL,
unit_price numeric NOT NULL,
line_total numeric GENERATED ALWAYS AS (qty * unit_price) VIRTUAL,
line_total_stored numeric GENERATED ALWAYS AS (qty * unit_price) STORED
);
A generated column cannot be written to directly. In INSERT or UPDATE, you may specify DEFAULT, but not a literal value. That is why a careless dump that inserts every displayed column will fail on restore.
Version caveat: virtual generated columns exist only on PostgreSQL 18+. Stored generated columns have been available since PostgreSQL 12. If you restore a ... VIRTUAL table onto PostgreSQL 17 or earlier, the server will reject the DDL.
What HiDataLinker actually ships
HiDataLinker v1.3.0 improved PostgreSQL DDL, Dump, and migrate fidelity so virtual generated columns (and partition definitions, comments, and sequences) are no longer dropped on the export path.
Concretely:
- Table DDL is emitted with
GENERATED ALWAYS AS (expression) VIRTUALor... STORED, including the generation expression. - Data
INSERTs skip generated columns so PostgreSQL can compute them. (GENERATED ALWAYS AS IDENTITYis handled separately withOVERRIDING SYSTEM VALUEwhen needed.) - Column metadata lists whether a column is
GENERATED ALWAYS AS VIRTUALorSTORED. You can also run the officialCREATE TABLE/ALTER TABLESQL in the editor. - Connections stay on your machine. Paste a
DATABASE_URLor fill host fields; there is no seat, connection, or tab freemium wall in the MIT core. - Same-engine migrate is PostgreSQL → PostgreSQL (or MySQL → MySQL). This is not cross-engine ETL.
What it does not ship: a visual generated-column designer, a uuidv7 picker, or a guarantee that every pg_dump object type is reproduced. Create or change generation expressions in the SQL editor. Structure-panel edits for generated columns are limited.
Practical path: dump, then migrate
- Connect locally. Add the source (and the target, if you will migrate) as HiDataLinker connections. Paste
DATABASE_URLor enter host, port, user, and database. Credentials stay on the desktop. - Confirm the server version. Virtual generated columns need PostgreSQL 18 or later on both sides. Run
SELECT version();in the SQL editor. - Inspect or create the columns. Check column metadata for
GENERATED ALWAYS AS VIRTUALorSTORED, or run theCREATE TABLEabove in the editor. - Dump to a
.sqlfile when you want a reviewable backup. Confirm thatCREATE TABLEstill carries theVIRTUALorSTOREDclause, and thatINSERTlists only base columns such assku,qty, andunit_price. - Same-engine migrate when you want a connection-to-connection copy. Choose PostgreSQL → PostgreSQL, confirm the target name, and prefer staging → local when the source is production-like.
If the target is older than PostgreSQL 18, rewrite virtual columns as STORED (or drop them) before you migrate — HiDataLinker will not silently downgrade the clause for you.
Honest v1 limits
v1 markets MySQL and PostgreSQL only. Dump and migrate are same-engine. The workbench is a native Tauri desktop app, not a heavy Java or Electron shell, but it is not a replacement for pg_dump / pg_restore in every edge case. Privileges on generation expressions, logical replication of generated columns, and the restriction that virtual expressions may use only built-in types and functions are server rules — read the official docs rather than expecting the GUI to enforce them.
Source: github.com/henrybit/HiDataLinker · Overview: /db-client
Soft next step
Need a desktop client that can dump and migrate PostgreSQL virtual generated columns on PostgreSQL 18+, without a freemium wall?
- Download: https://hidatalinker.onesolo.app/
- Source: https://github.com/henrybit/HiDataLinker