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;
VALUES (288, 1, NULL::smallint, NULL::smallint), ...
text
is inferred from, in similar cases I'd expectunknown
in its place.NULL
s are.