`column "..." specified more than once` error of view in PostgreSQL

Question

I have a large query in a PostgreSQL database. The Query is something like this:

SELECT * FROM table1, table2, ... WHERE table1.id = table2.id...

When I run this query as a sql query, the it returns the wanted row.

But when I tries to use the same query to create a view, it returns an error:

error: column "id" specified more than once.

(I use pgAdminIII when executing the queries.)

I'll guess this happens because the resultset will have more than one column named "id". Is there someway to solve this, without writing all the column names in the query?

Vinko Vrsalovic · Accepted Answer · 2008-10-16 10:02:59Z

33

That happens because a view would have two id named columns, one from table1 and one from table2, because of the select *.

You need to specify which id you want in the view.

SELECT table1.id, column2, column3, ... FROM table1, table2 
WHERE table1.id = table2.id

The query works because it can have equally named columns...

postgres=# select 1 as a, 2 as a;
 a | a
---+---
 1 | 2
(1 row)

postgres=# create view foobar as select 1 as a, 2 as a;
ERROR:  column "a" duplicated
postgres=# create view foobar as select 1 as a, 2 as b;
CREATE VIEW

answered Oct 16, 2008 at 10:02

Vinko Vrsalovic

342k55 gold badges341 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nnyby Over a year ago

Is there a way to automatically prepend table names to column names in the result of a SELECT * query?

fccoelho Over a year ago

The problem with this solution is when you have too many columns to manually write on your select statement

user80168 · Accepted Answer · 2009-09-29 17:25:49Z

19

If only join columns are duplicated (i.e. have the same names), then you can get away with changing:

select *
from a, b
where a.id = b.id

to:

select *
from a join b using (id)

answered Sep 29, 2009 at 17:25

user80168

1 Comment

escargot agile Over a year ago

This didn't fix the error, but it is a nicer syntax anyway.

Dimitry Svirinovsky · Accepted Answer · 2023-05-01 17:19:30Z

0

For me, it happened due to my mass in adding columns. There were 5 columns in the previous version of create view statement. I added 3 new ones in the final select but added only 2 to the list of columns at the create view statement beginning. After fixing the column list the issue is gone. As for me error msg is not helpful.

answered May 1, 2023 at 17:19

Dimitry Svirinovsky

1

Comments

Ben Wilson · Accepted Answer · 2017-08-15 03:08:06Z

-1

If you got here because you are trying to use a function like to_date and getting the "defined more than once" error, note that you need to use a column alias for functions, e.g.:

to_date(o.publication_date, 'DD/MM/YYYY') AS publication_date

answered Aug 15, 2017 at 3:08

Ben Wilson

2,7065 gold badges31 silver badges40 bronze badges

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-01 12:49:03Z

I got the same error below:

ERROR: column "?column?" specified more than once

When I tried to create the view which has 2 columns 'Hello' and 'World' with SELECT statement as shown below:

CREATE VIEW my_view AS
  SELECT 'Hello', 'World';

So, I set TEXT type to 'Hello' as shown below, then I could create the view without error. *The doc says the column name defaults to ?column?; also, the column data type defaults to text, ...:

CREATE VIEW my_view AS
  SELECT TEXT 'Hello', 'World';

Or, I used VALUES statement as shown below, then I could create the view without error:

CREATE VIEW my_view AS
  -- SELECT 'Hello', 'World';
  VALUES ('Hello', 'World');

In addition, If you try to create the view which has one column ('Hello', 'World') with SELECT statement as shown below:

CREATE VIEW my_view AS
  SELECT ('Hello', 'World');

Then, there is the error below:

ERROR: column "row" has pseudo-type record

Cade Roux · Accepted Answer · 2008-10-16 13:59:09Z

-3

No built-in way in the language to solve it (and frankly, * is a bad practice in general because it can cause latent defects to arise as the table schemas change - you can do table1.*, table2.acolumn, tabl2.bcolumn if you want all of one table and selectively from another), but if PostgreSQL supports INFORMATION_SCHEMA, you can do something like:

DECLARE @sql AS varchar

SELECT @sql = COALESCE(@sql + ', ', '') 
    + '[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'
    + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('table1', 'table2')
ORDER BY TABLE_NAME, ORDINAL_POSITION

PRINT @sql

And paste the results in to save a lot of typing. You will need to manually alias the columns which have the same name, of course. You can also code-gen unique names if you like (but I don't):

SELECT @sql = COALESCE(@sql + ', ', '') 
    + '[' + TABLE_NAME + '].[' + COLUMN_NAME + '] '
    + 'AS [' + TABLE_NAME + '_' + COLUMN_NAME + ']'
    + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('table1', 'table2')
ORDER BY TABLE_NAME, ORDINAL_POSITION

answered Oct 16, 2008 at 13:59

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

2 Comments

user80168 Over a year ago

there is nothing like @SOMETHING in PostgreSQL.

Magnus Hagander Over a year ago

There's also no using [] to quote identifiers. And not CHAR() function. And no PRINT statement.

Collectives™ on Stack Overflow

`column "..." specified more than once` error of view in PostgreSQL

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related