PostgreSQL dual equivalent for query

Question

I have Oracle Query as part of complex SQL script

"select sysdate into run_date from dual;"

The run_date is used elsewhere in the script.

I've converted this into PostgreSQL as

"select current_timestamp into lv_run_date"

since there is no dual equivalent in PostgreSQL. This works fine for first time and time stamp is stored.

However if run the query next time it throws error relation "run_date" already exists. I want current timestamp to be stored when it is run nexts time instead it throws error. How to fix it.

It looks like you've converted an Oracle PL/SQL script into a Postgre SQL query rather than into a Postgre PL/pgSQL script. INTO means "into a variable" in PL/pgSQL (just as in PL/SQL) , but means "into a table" (i.e. create table) in Postgre's SQL. — Thorsten Kettner
– Thorsten Kettner, Commented May 11, 2020 at 16:23

user330315 · Accepted Answer · 2020-05-11 16:18:41Z

5

The select wasn't necessary in PL/SQL to begin with, and it's not necessary in PL/pgSQL either. Just assign the value:

lv_run_date := current_timestamp;

answered May 11, 2020 at 16:18

user330315

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

Collectives™ on Stack Overflow

PostgreSQL dual equivalent for query

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