Postgresql – CREATE VIEW as join between tables with identically named columns

dynamic-sqlnaming conventionpostgresqlview

As the title says, I'd like to create a view from a join between tables which have columns with identical names (surprisingly "id").

Postgres doesn't prepend the table name to the result, but for some reason the query works fine, returning multiple columns with the same name and different data.

CREATE TABLE is less forgiving, though, and returns:

ERROR: column "id" specified more than once

Others have suggested SELECTing the columns under a different name (e.g. here), but there are many columns to both tables.

Do I need to somehow automatically rename / remove the id columns (such as this way), or is there a better way?

Best Answer

There was a bug in my answer to the question you referenced. While being at it, I improved some other details:

Prepend table name to each column in a result set in SQL? (Postgres specifically)

If you have many columns and / or for repeated use, I would use dynamic SQL as outlined over there.

If you'd been using a proper naming convention, you could avoid most of these cases. Never use id as column name, it's too ambiguous as you are just finding out the hard way. I would use tbl_id instead, "tbl" being the table name.
Unfortunately, some ORMs work with this anti-pattern.

How to implement a many-to-many relationship in PostgreSQL?

And you do not get any exception for a plain SELECT because Postgres does not require distinct column names in a plain SELECT (although the usefulness of this is limited beyond ad-hoc queries). Distinct names are required for columns of a table or view, though - even derived tables in subqueries. The manual:

Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display, but when the SELECT is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query.

Postgres has no need for distinct labels. But actual column names must be distinct to avoid ambiguities.

Related Solutions

Postgresql – Can’t rename columns in PostgreSQL views with CREATE OR REPLACE

I can reproduce your error ... in my case, I created a column first as 'date' then as 'x' (was trying to see if it was an issue with a reserved word; it wasn't:

ERROR:  cannot change name of view column "date" to "x"

If you issue a drop view first, it'll let you re-create the view with a changed name. I have no idea why create or replace won't do it.

Clarification by Colin 't Hart:

The documentation for CREATE VIEW explains it pretty well, I think:

The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

Sql-server – Refactoring sql for tables with similar columns but different table names

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter 
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0 
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0 
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0 
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0 
BREAK
END
RETURN
END


GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'


DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function 
FROM    dbo.fnSplit(@varyears, ',')


EXEC ( @combinedString )

Best Answer

Related Solutions

Postgresql – Can’t rename columns in PostgreSQL views with CREATE OR REPLACE

Sql-server – Refactoring sql for tables with similar columns but different table names

Related Question