Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: if exists update else insert

    Hello,

    I would like to do that, "if exists update else insert".

    This is my query (executed from a java program), but it's not working and don't know why:
    Code:
    String queryPostgresql = "if exists(select oid from trabajos where oid = "+ oid +") update trabajos set (uid = "+ uid +", fecha_fin = to_timestamp("+ etime +")) values ("+ uid +", to_timestamp("+ etime +") ELSE insert into trabajos (uid, fecha_fin) values ("+ uid +", to_timestamp("+ etime +") where oid = "+ oid +"";
    This i think would work in mysql (maybe the syntax isn't ok).

    Any help?

    Thanks.
    Last edited by ciglesias; 04-13-11 at 10:20.

  2. #2
    Join Date
    Dec 2010
    Location
    Leipzig (Germany)
    Posts
    12
    Can you please turn in a structured way?
    Use this [*CODE] [*/CODE] without *.

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    Quote Originally Posted by Indarija View Post
    Can you please turn in a structured way?
    Use this [*CODE] [*/CODE] without *.
    Of course.
    Done.

  4. #4
    Join Date
    Dec 2010
    Location
    Leipzig (Germany)
    Posts
    12
    No, I mean it so:

    Code:
    String queryPostgresql = "
    	if exists(select oid from trabajos where oid = "+ oid +") 
    		update trabajos set (uid = "+ uid +", fecha_fin = to_timestamp("+ etime +")) values ("+ uid +", to_timestamp("+ etime +") 
    	ELSE 
    		insert into trabajos (uid, fecha_fin) values ("+ uid +", to_timestamp("+ etime +") where oid = "+ oid +"
    ";
    I've never used PostgreSQL with Java but I think the query is not correct

    Code:
    String queryPostgresql = "
    	if exists(select oid from trabajos where oid = "+ oid +")  THEN
    		update trabajos set (uid = "+ uid +", fecha_fin = to_timestamp("+ etime +")) values ("+ uid +", to_timestamp("+ etime +") ;
    	ELSE 
    		insert into trabajos (uid, fecha_fin) values ("+ uid +", to_timestamp("+ etime +") where oid = "+ oid +";
            END IF;
    ;
    And check the functions: to_timestamp(), exists() and so on.

    to_timestamp() for example need 2 parameter:
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Data Type Formatting Functions

  5. #5
    Join Date
    Apr 2011
    Posts
    4
    Thank you,

    I've created a function "merge_db" and using it in the java code.

    It's actually working just right, but postgresql returns something and java throws an exception because I don't know how to handle it.

    Thank you.

    EDIT: Done, I was using executeUpdate when I needed executeQuery.
    Last edited by ciglesias; 04-14-11 at 04:41.

Posting Permissions

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