Postgres error "ERROR: INSERT has more target columns than expressions"

Question

I have the following tables :

CREATE TABLE public.participant_audit
(
  participant_audit_id bigint NOT NULL DEFAULT nextval('participant_audit_participant_audit_id_seq'::regclass),
  participant_id bigint,
  shared_asset_id bigint NOT NULL,
  asset_role_type character varying(200) NOT NULL,
  user_external_ref_uuid uuid NOT NULL,
  user_first_name character varying(200) NOT NULL,
  user_last_name character varying(200) NOT NULL,
  user_email_address character varying(200) NOT NULL,
  deleted_timestamp timestamp(0) with time zone,
  row_updated_timestamp timestamp(6) with time zone NOT NULL,
  row_created_timestamp timestamp(6) with time zone NOT NULL,
  row_created_by_db_user oid NOT NULL,
  row_updated_by_db_user oid NOT NULL,
  created_by_client uuid,
  updated_by_client uuid,
  CONSTRAINT participant_audit_pkey PRIMARY KEY (participant_audit_id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE public.participant
(
  participant_id bigint NOT NULL DEFAULT nextval('participant_participant_id_seq'::regclass),
  shared_asset_id bigint NOT NULL,
  asset_role_type_id bigint NOT NULL,
  user_external_ref_uuid uuid NOT NULL,
  user_first_name character varying(200) NOT NULL,
  user_last_name character varying(200) NOT NULL,
  user_email_address character varying(200) NOT NULL,
  deleted_timestamp timestamp(0) with time zone,
  row_updated_timestamp timestamp(6) with time zone NOT NULL,
  row_created_timestamp timestamp(6) with time zone NOT NULL,
  row_created_by_db_user oid NOT NULL,
  row_updated_by_db_user oid NOT NULL,
  created_by_client uuid,
  updated_by_client uuid,
  CONSTRAINT participant_pkey PRIMARY KEY (participant_id),
  CONSTRAINT participant_asset_role_type_id_fkey FOREIGN KEY (asset_role_type_id)
      REFERENCES public.asset_role_type (asset_role_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT participant_shared_asset_id_fkey FOREIGN KEY (shared_asset_id)
      REFERENCES public.shared_asset (shared_asset_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

And the following TRIGGER FUNCTION:

-- DROP FUNCTION public.participant_audit();

CREATE OR REPLACE FUNCTION public.participant_audit()
  RETURNS trigger AS
$BODY$
    BEGIN
        insert into participant_audit
                    (participant_audit_id, participant_id , shared_asset_id , asset_role_type , user_external_ref_uuid,
                      user_first_name , user_last_name , user_email_address , deleted_timestamp, row_updated_timestamp,
                      row_created_timestamp , row_created_by_db_user , row_updated_by_db_user , created_by_client,
                      updated_by_client
                    )
                   select NEW.* ;
                    RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

When I execute the following INSERT

INSERT INTO participant (shared_asset_id,asset_role_type_id,
  user_external_ref_uuid,user_first_name,user_last_name,
  user_email_address,row_created_by_db_user,
  row_updated_by_db_user,created_by_client,updated_by_client) 
VALUES (1, 1, 'c9d140ad-b0da-4a9d-a898-8719000c7b7b'::uuid , 'john', 'simpson', '[email protected]', 1::oid,1::oid, '53ed670d-f680-4e81-b53d-59b3d487633f'::uuid, '53ed670d-f680-4e81-b53d-59b3d487633f'::uuid);

I get the following error:

ERROR: INSERT has more target columns than expressions LINE 2: ...user , row_updated_by_db_user , created_by_client,updated_by... ^ QUERY: insert into public.participant_audit (participant_audit_id, participant_id , shared_asset_id , asset_role_type , user_external_ref_uuid,user_first_name , user_last_name , user_email_address , deleted_timestamp, row_updated_timestamp,row_created_timestamp , row_created_by_db_user , row_updated_by_db_user , created_by_client,updated_by_client) select NEW.* CONTEXT: PL/pgSQL function participant_audit() line 3 at SQL statement

********** Error **********

ERROR: INSERT has more target columns than expressions SQL state: 42601 Context: PL/pgSQL function participant_audit() line 3 at SQL statement

How can I fix this issue ??

You're not adding enough columns in your insert. Either remove updated_by_client from before values, or add a value for updated_by_client — Alejandro C.
– Alejandro C., Commented Dec 5, 2016 at 22:01
I executed the following and still got the above error: INSERT INTO participant (shared_asset_id,asset_role_type_id,user_external_ref_uuid,user_first_name,user_last_name,user_email_address,row_created_by_db_user ,row_updated_by_db_user) values (1, 1, 'c9d140ad-b0da-4a9d-a898-8719000c7b7b'::uuid , 'john', 'simpson', '[email protected]', 1::oid,1:oid); — XnaijaZ
– XnaijaZ, Commented Dec 5, 2016 at 22:38
I don't think you meant to tag this plsql as the question has nothing to do with Oracle. — William Robertson
– William Robertson, Commented Dec 6, 2016 at 12:40

e4c5 · Accepted Answer · 2016-12-06 00:13:40Z

1

The problem is in your trigger. Count the columns that you are trying to insert into the audit table here.

insert into participant_audit
                    (participant_audit_id, participant_id , shared_asset_id , asset_role_type , user_external_ref_uuid,
                      user_first_name , user_last_name , user_email_address , deleted_timestamp, row_updated_timestamp,
                      row_created_timestamp , row_created_by_db_user , row_updated_by_db_user , created_by_client,
                      updated_by_client
                    )
                   select NEW.* ;

That's quite a few more than what's contained in NEW because your insert statement has only 10 columns in it. I believe some of your columns maybe taking NULL values. Pass nulls explicitly in the SELECT part of your statement inside the trigger.

answered Dec 6, 2016 at 0:13

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

Postgres error "ERROR: INSERT has more target columns than expressions"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related