Trying to understand pg_get_expr()

Em 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 pg_get_expr(adbin, adrelid) returns 'test'::character varying
so it differs from 'test'

regards
Marcos

On 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 pg_get_expr(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;

adrelid | pg_typeof | pg_get_expr
---------+-----------+-------------

I also tried other combinations of casting both sides of "=" and it
still did not work.

regards
Marcos

--
Adrian Klaver
adrian.klaver@aklaver.com

Em 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
still did not work.

"'test'::character varying" is the result of that function, not type of test
This should work
AND pg_get_expr(adbin, adrelid) = $$'test'::character varying$$;

regards
Marcos

On 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
AND pg_get_expr(adbin, adrelid) = $$'test'::character varying$$;

Yeah that worked.

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;

adrelid | pg_typeof | pg_get_expr
--------------+-----------+---------------------------
default_test | text | 'test'::character varying
default_test | text | 0

Why is the second case not?:

'0'::integer

regards
Marcos

--
Adrian Klaver
adrian.klaver@aklaver.com

Em 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

CREATE 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;

regards
Marcs

Adrian Klaver <adrian.klaver@aklaver.com> writes:

adrelid | pg_typeof | pg_get_expr
--------------+-----------+---------------------------
default_test | text | 'test'::character varying
default_test | text | 0

Why is the second case not?:
'0'::integer

PG's parser automatically attributes type integer to an unadorned
integer literal, so no cast is necessary there, and pg_get_expr
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 pg_get_expr 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

Em 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
integer literal, so no cast is necessary there, and pg_get_expr
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 pg_get_expr 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

Thanks Tom

If your fields default to a string, then all them will have to cast back to
its type when calling that function.

CREATE 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'
);

regards
Marcos