if exists update else insert

ciglesias · #1 (**permalink**) 04-12-11, 12:21

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.

Indarija · #2 (**permalink**) 04-13-11, 09:04

Can you please turn in a structured way?
Use this [*CODE] [*/CODE] without *.

ciglesias · #3 (**permalink**) 04-13-11, 09:21

Quote:

Originally Posted by Indarija

Can you please turn in a structured way?
Use this [*CODE] [*/CODE] without *.

Of course.
Done.

Indarija · #4 (**permalink**) 04-13-11, 11:09

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

ciglesias · #5 (**permalink**) 04-13-11, 11:20

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech