The following bug has been logged on the website:
Bug reference: 19445
Logged by: Xianghang Zheng
Email address: zheng_xianghang@163.com
PostgreSQL version: 18.3
Operating system: Linux x86_64
Description:
1. PostgreSQL Version
PostgreSQL 18.3 (x86_64)
2. Operating System
Linux x86_64
3. Problem Description
When a table column uses a domain that has a DEFAULT value, the column's
pg_attribute.atthasdef is false, and no entry exists in pg_attrdef.
However, the domain default value works correctly at runtime.
This is a system catalog metadata bug.
4. Steps to Reproduce
CREATE DOMAIN my_arr_domain AS int[] DEFAULT '{}';
CREATE TABLE t (col my_arr_domain);
SELECT
a.attname,
a.atthasdef,
pg_get_expr(adbin, adrelid) as default_value
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = 't'::regclass AND a.attnum > 0;
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;
5. Actual Result
atthasdef = f
default_value is empty
But insert returns the correct default {}
6. Expected Result
atthasdef should be true
System catalog must correctly reflect the default inherited from the domain
7. Additional Information
- Domain default works correctly
- System metadata is wrong
- Affects pg_dump, information_schema, and 3rd party tools
- Bug exists in latest stable PostgreSQL 18.3
-----------------------------------------------------------------------------------------------------
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
postgres=# DROP TABLE IF EXISTS t CASCADE;
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;
DROP TABLE t;
DROP DOMAIN my_arr_domain CASCADE;DROP TABLE
postgres=# DROP DOMAIN IF EXISTS my_arr_domain CASCADE;
DROP DOMAIN
postgres=#
postgres=# CREATE DOMAIN my_arr_domain AS int[] DEFAULT '{}';
CREATE DOMAIN
postgres=#
postgres=# CREATE TABLE t (col my_arr_domain);
CREATE TABLE
postgres=#
postgres=# SELECT
postgres-# a.attname,
postgres-# a.atthasdef,
postgres-# pg_get_expr(adbin, adrelid) as default_value
postgres-# FROM pg_attribute a
postgres-# LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum =
d.adnum
postgres-# WHERE a.attrelid = 't'::regclass
postgres-# AND a.attnum > 0;
attname | atthasdef | default_value
---------+-----------+---------------
col | f |
(1 row)
postgres=#
postgres=# INSERT INTO t DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT * FROM t;
col
-----
{}
(1 row)
postgres=# DROP TABLE t;
DROP TABLE
postgres=# DROP DOMAIN my_arr_domain CASCADE;
DROP DOMAIN
postgres=#
