Adding values to a newly inserted column in an existing table in PostgreSQL 9.3

Question

I've created a table named collegetable:

create table collegetable 
(
    stid integer primary key not null,
    stname varchar(50),
    department varchar(10),
    dateofjoin date
);

I provided values for each column:

collegetable data

I inserted a new column named "cgpa" and tried to add values for this column in one shot using the code:

WITH col(stid, cgpa) AS 
    (VALUES((1121,8.01),
            (1131,7.12),
            (1141,9.86))
)        
UPDATE collegetable AS colldata
SET cgpa = col.cgpa
FROM col
WHERE colldata.stid = col.stid;

I got this error :

ERROR:operator does not exist:integer=record
LINE9:where colldata.stid=col.stid;
HINT:No operator matches the given name and argument type. You might need to add explicit type casts.

Please help me solve this. Thanks in advance.

Answer 1

The with clause only defines the names of the columns, not the data types:

with col (stid, cgpa) as (
  ...
)
update ...;

For details see the tutorial and the full reference