CODEWITHSUNDEEP
postgresql
user1165791
user1165791

Reputation: 111

ERROR: operator is not unique: unknown + unknown

I have a PostGIS database and I have to compute new values for rows in a new column. These values should be the average of the values of several columns. So I do the query:

INSERT INTO bdps (da_m) 
VALUES (avg('da_1'+'da_2'+'da_3'+'da_4'+'da_5'+'da_6'+'da_7'));

In this query bdps is my database, da_m is the new column and da_1 to da_7 are existing columns that have double precision type.

da_m was created using

ALTER TABLE bdps ADD COLUMN da_m double precision;

I get the following error:

    ERROR:  operator is not unique: unknown + unknown
LINE 2:  VALUES (avg('da_1'+'da_2'+'da_3'+'da_4'+'da_5'+'da_6'+'da_7...
                           ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

********** Error **********

ERROR: operator is not unique: unknown + unknown
SQL state: 42725
Hint: Could not choose a best candidate operator. You might need to add explicit type casts.
Character: 45

I google it and It has something to do with casts. I run PostGreSQL 8.3 and the database is under PostGIS 1.5, on Windows 7.

Upvotes: 0

Views: 17795

Answers (1)

mu is too short
mu is too short

Reputation: 434665

You can produce an "inlined table" using VALUES and then apply your average to that:

update bdps
set da_m = (
    select avg(x)
    from (values (da_1), (da_2), (da_3), (da_4), (da_5), (da_6), (da_7)) as dt(x)
)

that saves you from messing around with COALESCE, SIGN, counting columns by hand, etc.

And as a_horse_with_no_name (but with SQL and PostgreSQL skills) mentioned:

  • Don't use single quotes with column names, table names, or other identifiers. Use double quotes if you need to worry about upper/lower case issues or strange characters (or better, rename your columns and save some grief).
  • Use UPDATE to update existing rows, INSERT for is adding new rows.

Upvotes: 3

Related Questions