The following bug has been logged on the website:
Bug reference: 17486
Logged by: Nicolas Lutic
Email address: n.lutic@loxodata.com
PostgreSQL version: 14.3
Operating system: Debian 11
Description:
Hi Team,
I found something weird. Restoring a view fails if this view contains an
attribute without alias name.
Please find below the details to reproduce the problem:
psql -h localhost -c 'CREATE DATABASE demo;'
psql -h localhost -d demo
demo=# CREATE VIEW v_static_select as
WITH static_select as (
select
1 as foo,
'text'
)
select * from static_select;
demo=# \d+ v_static_select
View "public.v_static_select"
Column | Type | Collation | Nullable | Default | Storage |
Description
----------+---------+-----------+----------+---------+----------+-------------
foo | integer | | | | plain |
?column? | text | | | | extended |
View definition:
WITH static_select AS (
SELECT 1 AS foo,
'text'::text
)
SELECT static_select.foo,
static_select."?column?"
FROM static_select;
demo=# select * from v_static_select;
foo | ?column?
-----+----------
1 | text
(1 row)
pg_dump -h localhost -p5432 -U postgres -d demo -Fc -f /tmp/demo.dump
psql -h localhost -p5432 -U postgres -d demo -c 'DROP VIEW
v_static_select ;'
pg_restore -s -h localhost -p5432 -U postgres -d demo /tmp/demo.dump
pg_restore: error: could not execute query: ERROR: column
static_select.?column? does not exist
LINE 7: static_select."?column?"
^
Command was: CREATE VIEW public.v_static_select AS
WITH static_select AS (
SELECT 1 AS foo,
'text'::text
)
SELECT static_select.foo,
static_select."?column?"
FROM static_select;
Regards, Nicolas Lutic