CODEWITHSUNDEEP
sqldatabasepostgresqlpgadminsql-view
eflles
eflles

Reputation: 6846

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

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?

Upvotes: 28

Views: 48386

Answers (6)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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

Upvotes: -1

Dimitry Svirinovsky
Dimitry Svirinovsky

Reputation: 1

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.

Upvotes: 0

Ben Wilson
Ben Wilson

Reputation: 2676

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

Upvotes: -1

user80168
user80168

Reputation:

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)

Upvotes: 18

Cade Roux
Cade Roux

Reputation: 89661

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

Upvotes: -3

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340241

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

Upvotes: 32

Related Questions