postgresql insert with integer PK nextval

Question

So, I have the following table definition with the id as integer PK using a nextval seq as shown below. '''

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
)

''' I then want to use a function as shown below to insert omitting the id since it should pull the default value but I am getting the following error. Not sure where to go from here.... '''

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

Laurenz Albe · Accepted Answer · 2020-06-23 05:52:24Z

1

You need to specify column names if you are not setting a value for each table column:

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

Alternatively, you can insert DEFAULT in place of the column to explicitly choose the default value. But specifying the columns is preferable.

edited Jun 23, 2020 at 5:52

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

answered Jun 23, 2020 at 5:43

pifor

7,9722 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Berger Over a year ago

of course I do. thanks a lot for your help. I should have seen that.

Collectives™ on Stack Overflow

postgresql insert with integer PK nextval

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related