Results 1 to 2 of 2

Thread: Upsert in 9.5+

  1. #1
    Join Date
    Jan 2016
    Posts
    13

    Unanswered: Upsert in 9.5+

    Code:
    create sequence test_id_seq
    
    CREATE TABLE public.test
    (
      id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
      name varchar,
      is_active boolean NOT NULL DEFAULT true,
      CONSTRAINT pk_test PRIMARY KEY (id)
    )
    WITH (
      OIDS=FALSE
    );
    
    insert into public.test(id,name) 
    select 1 as id,'h4ellso1' as name
    ON CONFLICT (id) DO UPDATE 
    		SET 
    		name=excluded.name
    		;
    The above code works fine. But I am having issues in the case where I dont pass the ID in the insert. Since this is auto generated by sequence I dont have to explicitly pass value. Is there anyway its possible? I need something like this

    Code:
    insert into public.test(name) 
    select 1 as id,'h4ellso1' as name
    ON CONFLICT (id) DO UPDATE 
    		SET 
    		name=excluded.name
    		;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Unrelated, but: are you aware that you don't need that (somewhat) complicated select to supply values for the insert clause?
    You can simply write:
    Code:
    insert into test (a,b) values (1,2);
    But I am having issues in the case where I dont pass the ID in the insert
    You are not required to use a column from the insert list, so your code:
    Code:
    insert into test (name) 
    values ('h4ellso1')
    ON CONFLICT (id) 
    DO UPDATE 
      SET name=excluded.name;
    will work just fine.

    Alternatively, you can specify a constraint name instead of a column list in the on conflict clause.
    Code:
    insert into test (name) 
    values ('h4ellso1')
    ON CONFLICT on constraint pk_test DO 
    UPDATE 
      SET name=excluded.name;
    However as the id is generated through a sequence this seems rather an overkill as that will only give a conflict if you inserted values into that column manually bypassing the sequence generation (which is something you shouldn't do in the first place)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •