postgresql insert with integer PK nextval

Question

所以，我有下表定义，其中 id 为整数 PK，使用 nextval seq 如下所示。 '''

CREATE TABLE public.fi_raisedalarms
(
id integer NOT NULL DEFAULT nextval('fi_raisedalarms_id_seq'::regclass),
equipid integer,
alid integer,
isset boolean,
tstamp timestamp without time zone,
create_uid integer,
create_date timestamp without time zone,
write_uid integer,
write_date timestamp without time zone,
CONSTRAINT fi_raisedalarms_pkey PRIMARY KEY (id),
CONSTRAINT fi_raisedalarms_alid_fkey FOREIGN KEY (alid)
REFERENCES public.fi_alarms (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE SET NULL,
CONSTRAINT fi_raisedalarms_create_uid_fkey FOREIGN KEY (create_uid)
REFERENCES public.res_users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE SET NULL,
CONSTRAINT fi_raisedalarms_write_uid_fkey FOREIGN KEY (write_uid)
REFERENCES public.res_users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE SET NULL
)

''' 然后我想使用如下所示的函数插入省略 id，因为它应该拉取默认值，但我收到以下错误。不知道从这里去哪里... '''

CREATE OR REPLACE FUNCTION public.setequipmentalarm(
equipmentid integer,
alarmid integer,
isset boolean,
tstamp timestamp without time zone)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
AS $BODY$
DECLARE
var integer;
BEGIN
INSERT INTO fi_raisedalarms VALUES(equipmentid, alarmid, isset, tstamp) RETURNING equipmentid into var;
RETURN var;
END;
$BODY$;
ALTER FUNCTION public.setequipmentalarm(integer, integer, boolean, timestamp without time zone)
OWNER TO postgres;

'''

ERROR:  column "alid" is of type integer but expression is of type boolean
LINE 1: ...INTO fi_raisedalarms VALUES(equipmentid, alarmid, isset, tst...
^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO fi_raisedalarms VALUES(equipmentid, alarmid, isset, tstamp) RETURNING equipmentid
CONTEXT:  PL/pgSQL function setequipmentalarm(integer,integer,boolean,timestamp without time zone) line 5 at SQL statement
SQL state: 42804

Answer 1

如果未为每个表列设置值，则需要指定列名：

INSERT INTO fi_raisedalarms(equipid, alid , isset, tstamp) VALUES ...

或者，可以在列的位置插入DEFAULT以显式选择默认值。但最好指定列。