Duplicate columns are allowed in materialized views?

[Linksku]

What version of Materialize are you using?

materialized v0.9.10 (8d7b550fb)

How did you install Materialize?

• Docker image
• Linux release tarball
• APT package
• macOS release tarball
• Homebrew tap
• Built from source
• Materialize Cloud

What was the issue?

Materialize allows multiple columns with the same name. E.g.:

materialize=> create materialized view test as id, id, id from posts;
CREATE VIEW
materialize=> select * from test;
 id | id | id 
----+----+----
  1 |  1 |  1 
  2 |  2 |  2
  3 |  3 |  3

This prevents selecting the duplicate column:

materialize=> select id from test;
ERROR:  column name "id" is ambiguous

I'm relatively new to Postgres, but it looks like this isn't allowed in Postgres:

foo=# create materialized view test as id, id, id from posts;
ERROR:  column "id" specified more than once

Is the issue reproducible? If so, please provide reproduction instructions.

The queries above consistently repro.

Please attach any applicable log files.

Nothing relevant

[umanwizard]

You can rename the columns in the select query:

materialize=> create table t (id int);
CREATE TABLE
materialize=> insert into t values (1);
INSERT 0 1
materialize=> create view v as select id, id + 1 id from t;
CREATE VIEW
materialize=> select * from v;
 id | id 
----+----
  1 |  2
(1 row)
materialize=> select id_b from v as v (id_a, id_b);
 id_b 
------
    2
(1 row)

[Linksku]

Thanks for checking, seems like this is an intended feature. I'm curious why duplicate column names are allowed?

In my case, it was caused by a bug. I don't see why anyone would intentionally have duplicate column names.

[sploiselle]

@Linksku I think @umanwizard's suggestion is a viable workaround in the short term but am putting up a PR to prohibit this behavior in the next release.