Updating rows of updatable view with ResultSet.updateRow function

Question

I have updatable view on PostgreSQL server.

Update query works fine when I execute it from pgAnmin3 console, but when I try to update this view with ResultSet.updateRow() method, I get the following error:

org.postgresql.util.PSQLException: No primary key found for table

I guess I can't specify primary key for view.

Can I specify key columns for ResultSet.updateRow() method in my client application? Or can I specify a WHERE clause for ResultSet.updateRow() method?

Here are my tables

CREATE TABLE fin.t_year
(
    id serial NOT NULL,
    date_begin date NOT NULL,
    date_end date NOT NULL, 
    year_name character varying(128),
    CONSTRAINT "PK_year" PRIMARY KEY (id)
)

WITH (
  OIDS=FALSE
);


CREATE TABLE fin.t_period
(
    id serial NOT NULL,
    id_year integer NOT NULL,
    per_begin date NOT NULL,
    per_end date NOT NULL, 
    per_name character varying(256),
    CONSTRAINT "PK_period" PRIMARY KEY (id),
    CONSTRAINT "FK_period_year" FOREIGN KEY (id_year)
    REFERENCES fin.t_year (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

WITH (
  OIDS=FALSE
);


CREATE VIEW fin.vi_period AS 
     SELECT per.id,
        per.per_begin AS "Begin",
        per.per_end AS "End",
        per.per_name AS "Name",
        y.year_name AS "Year"
       FROM fin.t_period per
         JOIN fin.t_year y ON y.id = per.id_year;



CREATE OR REPLACE FUNCTION fin.tgfn_vi_period_update()
RETURNS trigger AS
$BODY$
    DECLARE
        id_row INTEGER;
        id_curr INTEGER;
        result RECORD;
BEGIN
        id_curr = NEW.id;

        -- Replace text identifier with integer primary key
        IF NEW."Year" IS NOT NULL THEN

            SELECT id INTO id_row
                FROM fin.t_year
                WHERE year_name = NEW."Year";
            UPDATE fin.t_period SET id_year = id_row 
                WHERE id = id_curr;

        END IF;


        IF NEW."Begin" IS NOT NULL THEN    

            UPDATE fin.t_period SET per_begin = NEW."Begin"
                WHERE id = id_curr;
        END IF;
        IF NEW."End" IS NOT NULL THEN    

            UPDATE fin.t_period SET per_end = NEW."End"
                WHERE id = id_curr;
        END IF;
        IF NEW."Name" IS NOT NULL THEN
            UPDATE fin.t_period SET per_name = NEW."Name"
                WHERE id = id_curr;
        END IF;



        SELECT * INTO result FROM fin.vi_period WHERE id = id_curr;
        RETURN  result;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;

This insert statement works fine

UPDATE fin.vi_period SET "Year" = 'new_year_name' WHERE id  = 10;

But the problem with this java code

statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.ExecuteQuery("SELECT * FROM fin.vi_period;");


rs.absolute(pos + 1);
rs.updateString("new_year_name");
rs.updateRow();

Can you give the structure of the table and the sql statement you are using to update the table. — Blip, Apr 28 '15 at 05:26

score 2 · Answer 1 · answered Apr 28 '15 at 07:15

The problem is that you are querying a view, and that view doesn't have a primary key (I am not sure if that is even possible with PostgreSQL, but most database don't support that). The JDBC driver requires a primary key to be able to make the result set updatable.

In other words: you cannot update this view through the result set. You either need to use an explicit UPDATE statement, or do this directly on the underlying table, not through the view.

Updating rows of updatable view with ResultSet.updateRow function

1 Answers1