2

I am using Postgres 9.4.6.

Is there any reason why the below update query would execute successfully here:-

UPDATE "Predictions" 
    SET "userid" = c."userid",
        "Fixture_No" = c."Fixture_No",
        "Home_Side_Score" = c."Home_Side_Score",
        "Away_Side_Score" = c."Away_Side_Score"
    FROM (VALUES(288, 1, NULL, NULL),(288, 34, 1, 1))
    AS c("userid", "Fixture_No", "Home_Side_Score", "Away_Side_Score")
    WHERE "Predictions"."Fixture_No" = c."Fixture_No" AND "Predictions"."userid" = c."userid"

But with these values, the same query would fail with "ERROR: column "Home_Side_Score" is of type smallint but expression is of type text LINE 4: "Home_Side_Score" = c."Home_Side_Score",":

UPDATE "Predictions" 
    SET "userid" = c."userid",
        "Fixture_No" = c."Fixture_No",
        "Home_Side_Score" = c."Home_Side_Score",
        "Away_Side_Score" = c."Away_Side_Score"
    FROM (VALUES(288, 1, NULL, NULL),(288, 34, NULL, NULL))
    AS c("userid", "Fixture_No", "Home_Side_Score", "Away_Side_Score")
    WHERE "Predictions"."Fixture_No" = c."Fixture_No" AND "Predictions"."userid" = c."userid"

This doesn't make any sense as I am not trying to pass postgres text instead of an integer?

My predictions table is as follows:

CREATE TABLE "Predictions"
(
  userid smallint,
  "Fixture_No" smallint,
  "Home_Side_Score" smallint,
  "Away_Side_Score" smallint,
  "Prediction_ID" bigserial NOT NULL,
  "Joker_Played" boolean,
  "Result" integer NOT NULL DEFAULT 4,
  CONSTRAINT "Predictions_pkey" PRIMARY KEY ("Prediction_ID"),
  CONSTRAINT "Result_Type" FOREIGN KEY ("Result")
      REFERENCES "ResultTypes" ("Result_ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Predictions"
  OWNER TO bjlizgykrnyffr;
6
  • 1
    You probably need to cast the values of the first group: VALUES (288, 1, NULL::smallint, NULL::smallint), ... May 11, 2016 at 12:56
  • What @ypercubeᵀᴹ says - no idea where the text is inferred from, in similar cases I'd expect unknown in its place. May 11, 2016 at 13:03
  • @ypercubeᵀᴹ But I don't understand why, in example one, it iterates through one set of NULL values but still executes successfully. May 11, 2016 at 13:09
  • @LemusThelroy I think it does not iterate in the strict sense of the word. From the second record it can infer which type the NULLs are. May 11, 2016 at 13:56
  • @dezso what can I change in the code to overcome this issue and allow me to pass two sets of null values if I wanted to? May 11, 2016 at 14:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.