pgsql-general
❮
Trying to understand pg_get_expr()
- Jump to comment-1Adrian Klaver<adrian.klaver@aklaver.com>Mar 17, 2026, 7:31 PM UTCGiven:
select version();
PostgreSQL 17.9 (Ubuntu 17.9-1.pgdg24.04+1)version -----------------------------------------------
and:
Then:CREATE TABLE default_test ( id integer, fld_1 varchar DEFAULT 'test', fld_2 integer DEFAULT 0 );SELECT adrelid::regclass, pg_typeof(pg_get_expr(adbin, adrelid)), pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'default_test'::regclass;and:adrelid pg_typeof pg_get_expr default_test text 'test'::character varying default_test text 0 SELECT adrelid::regclass, pg_typeof(pg_get_expr(adbin, adrelid)), pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'default_test'::regclass AND pg_get_expr(adbin, adrelid) = '0';adrelid pg_typeof pg_get_expr default_test text 0 SELECT adrelid::regclass, pg_typeof(pg_get_expr(adbin, adrelid)), pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'default_test'::regclass AND pg_get_expr(adbin, adrelid) = 'test';(0 rows)adrelid pg_typeof pg_get_expr
Why does the = 'test' not return anything?
--
Adrian Klaver
adrian.klaver@aklaver.com- Jump to comment-1Marcos Pegoraro<marcos@f10.com.br>Mar 17, 2026, 8:12 PM UTCEm ter., 17 de mar. de 2026 às 16:31, Adrian Klaver <
adrian.klaver@aklaver.com> escreveu:Why does the = 'test' not return anything?
for me pggetexpr(adbin, adrelid) returns 'test'::character varying
so it differs from 'test'
regards
Marcos- Jump to comment-1Adrian Klaver<adrian.klaver@aklaver.com>Mar 17, 2026, 8:19 PM UTCOn 3/17/26 1:08 PM, Marcos Pegoraro wrote:
Em ter., 17 de mar. de 2026 às 16:31, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> escreveu:
Why does the = 'test' not return anything?
for me pggetexpr(adbin, adrelid) returns 'test'::character varying
so it differs from 'test'
I should have indicated I tried casting:SELECT adrelid::regclass, pg_typeof(pg_get_expr(adbin, adrelid)), pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'default_test'::regclass AND pg_get_expr(adbin, 0) = 'test'::character varying;I also tried other combinations of casting both sides of "=" and it still did not work.adrelid pg_typeof pg_get_expr regards
Marcos
--
Adrian Klaver
adrian.klaver@aklaver.com- Jump to comment-1Marcos Pegoraro<marcos@f10.com.br>Mar 17, 2026, 8:27 PM UTCEm ter., 17 de mar. de 2026 às 17:19, Adrian Klaver <
adrian.klaver@aklaver.com> escreveu:I also tried other combinations of casting both sides of "=" and it
"'test'::character varying" is the result of that function, not type of test
still did not work.
This should work
regardsAND pg_get_expr(adbin, adrelid) = $$'test'::character varying$$;
Marcos- Jump to comment-1Adrian Klaver<adrian.klaver@aklaver.com>Mar 17, 2026, 8:36 PM UTCOn 3/17/26 1:26 PM, Marcos Pegoraro wrote:
Em ter., 17 de mar. de 2026 às 17:19, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> escreveu:
I also tried other combinations of casting both sides of "=" and it
still did not work.
"'test'::character varying" is the result of that function, not type of test
This should work
Yeah that worked.
AND pggetexpr(adbin, adrelid) = $$'test'::character varying$$;
It begs the question then, in:SELECT adrelid::regclass, pg_typeof(pg_get_expr(adbin, adrelid)), pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'default_test'::regclass;Why is the second case not?:adrelid pg_typeof pg_get_expr default_test text 'test'::character varying default_test text 0
'0'::integerregards
Marcos
adrian.klaver@aklaver.com
-- Adrian Klaver- Jump to comment-1Marcos Pegoraro<marcos@f10.com.br>Mar 17, 2026, 8:55 PM UTCEm ter., 17 de mar. de 2026 às 17:36, Adrian Klaver <
adrian.klaver@aklaver.com> escreveu:Why is the second case not?:
I don't know, but you can see that it's not only for integers
regardsCREATE TABLE default_test ( id integer, fld_1 varchar DEFAULT 'test', fld_2 integer DEFAULT 0, fld_3 date DEFAULT Current_Date, fld_4 timestamp DEFAULT Current_Timestamp, fld_5 text DEFAULT 'x', fld_6 boolean DEFAULT 'on', fld_7 int4range DEFAULT '[1,2)', fld_8 char DEFAULT '1' ); SELECT atttypid::regtype, pg_get_expr(adbin, adrelid) FROM pg_class c inner join pg_attribute a on c.oid = attrelid inner join pg_attrdef d on c.oid = d.adrelid and adnum = attnum WHERE relname = 'default_test' and attnum > 0;
Marcs - Jump to comment-1Tom Lane<tgl@sss.pgh.pa.us>Mar 17, 2026, 9:04 PM UTCAdrian Klaver <adrian.klaver@aklaver.com> writes:
adrelid | pgtypeof | pgget_expr
--------------+-----------+---------------------------
default_test | text | 'test'::character varying
default_test | text | 0Why is the second case not?:
PG's parser automatically attributes type integer to an unadorned
'0'::integer
integer literal, so no cast is necessary there, and pggetexpr
doesn't add one. But an unadorned string like 'test' does not
have a determinate type (well, it has type "unknown", but that
is an implementation artifact). We emit a cast construct to show
what type the constant was resolved as.
The bigger picture here is that pggetexpr relies on the same
code that is used for purposes like dumping views. We want the
output to be such that subexpressions of a view will certainly
be parsed as the same type they were interpreted as before.regards, tom lane- Jump to comment-1Marcos Pegoraro<marcos@f10.com.br>Mar 17, 2026, 9:12 PM UTCEm ter., 17 de mar. de 2026 às 18:04, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
PG's parser automatically attributes type integer to an unadorned
Thanks Tom
integer literal, so no cast is necessary there, and pggetexpr
doesn't add one. But an unadorned string like 'test' does not
have a determinate type (well, it has type "unknown", but that
is an implementation artifact). We emit a cast construct to show
what type the constant was resolved as.
The bigger picture here is that pggetexpr relies on the same
code that is used for purposes like dumping views. We want the
output to be such that subexpressions of a view will certainly
be parsed as the same type they were interpreted as before
If your fields default to a string, then all them will have to cast back to
its type when calling that function.
regardsCREATE TABLE default_test ( id integer, fld_1 varchar DEFAULT 'test', fld_2 integer DEFAULT '150'::text::integer, fld_3 date DEFAULT '2026/05/01', fld_4 timestamp DEFAULT '2026/05/01', fld_5 text DEFAULT 'x', fld_6 boolean DEFAULT 'on'::text::boolean, fld_7 int4range DEFAULT '[1,2)', fld_8 char DEFAULT '1' );
Marcos