{{ message }}
Repository navigation
Generated columns not supported in Postgres #1420
Description
Preconditions
- HeidiSQL version: 11.3.0.6295
- Database type and version: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
- OS: Debian Buster
Describe the bug
HeidiSQL doesn't let you create generated columns and doesn't handle existing columns correctly. Generated columns are the recommended way to create synthetic primary keys in Postgres.
To Reproduce
- Connect to a blank Postgres database and create a table using the Query tab:
CREATE TABLE accounts (
account_id INTEGER
NOT NULL
PRIMARY KEY
GENERATED ALWAYS AS IDENTITY,
current_balance NUMERIC
NOT NULL
DEFAULT 0
);- Find the table in the tree and open
CREATE codetab. The code shows shows noGENERATEDclause:
CREATE TABLE "accounts" (
"account_id" INTEGER NOT NULL,
"current_balance" NUMERIC NOT NULL DEFAULT '0',
PRIMARY KEY ("account_id")
)
;
COMMENT ON COLUMN "accounts"."account_id" IS '';
COMMENT ON COLUMN "accounts"."current_balance" IS '';- Right click on the tree and choose "Export database as SQL". The code generated shows no
GENERATEDclause:
-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
-- Server OS:
-- HeidiSQL Version: 11.3.0.6295
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Dumping structure for table casanuova.accounts
DROP TABLE IF EXISTS "accounts";
CREATE TABLE IF NOT EXISTS "accounts" (
"account_id" INTEGER NOT NULL,
"current_balance" NUMERIC NOT NULL DEFAULT '0',
PRIMARY KEY ("account_id")
);
-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;Screenshots or Crash reports
Activity
ansgarbecker commentedon Mar 25, 2026
HeidiSQL gets generation expressions through querying
pg_catalog.pg_attrdefforpg_get_expr(ad.adbin, ad.adrelid)- but such IDENDITY values are not returned from there. I need to probably tweak thatqGetTableColumnsquery.