postgresql insert with整数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以显式选择默认值。但是指定列更可取。