Equivalent to replace input date from User/API In PostgreSQL from Oracle

Question

I am in the process of transitioning Oracle queries to PostgreSQL and I am trying to pass a date range to pull back the results.

The original Oracle query is below:

select iss_id ,date_created from issues i where trunc(i.date_created) BETWEEN trunc(TO_DATE(:StartDt ,'mmddyyyy')) -- ^^^^^^^ AND trunc(TO_DATE(:EndDt ,'mmddyyyy')) -- ^^^^^^

My problem is with the :StartDt & :EndDt, when this is run in Oravle Developer it will produce a popup box that will allow the user to input a date value for these two variables. What I am looking for is an equivalent to this in PostgreSQL.

Any assistance to this would be greatly appreciated.

PostgreSQL is a server, while Oracle SQL Developer is a client. Asking what to put in parameterized values is the responsibility of the client: what client do you use to query your PostgreSQL server? — Guillaume Outters
– Guillaume Outters, Commented Nov 4 at 18:05
As said, this depends on your SQL client. It works fine in DataGrip. — Frank Heikens
– Frank Heikens, Commented Nov 4 at 18:08
This question is nearly a duplicate of this one: stackoverflow.com/questions/32995923/… — Bill Karwin
– Bill Karwin, Commented Nov 4 at 20:47
On a sidenote: It is a weekness in Oracle SQL Developer that you cannot pass a date as a bind variable, but must use a string instead. This is why the query uses TO_DATE. That it applies TRUNC then makes no sense, however. A better condition would be WHERE i.date_created >= TO_DATE(:startdt ,'mmddyyyy') AND i.date_created < TO_DATE(:enddt ,'mmddyyyy') + INTERVAL '1' DAY anyhow to allow for the use of an index on date_created and dealing which the fact that in Oracle there exists no pure date type, but only a datetime type (which they inappropriately call DATE). — Thorsten Kettner
– Thorsten Kettner, Commented Nov 12 at 14:00

Robert · Accepted Answer · 2025-11-04 19:43:45Z

You need to prompt for the variables.

Here is an example session from the psql command line client. It creates a table, then runs the query like you do, which gets an error. Then it prompts for the variable and runs the same query again. That time there is no error as the variable has now been entered.

robert@[local] postgres# create table foo(id int); CREATE TABLE robert@[local] postgres# select * from foo where id = :id; ERROR: syntax error at or near ":" LINE 1: select * from foo where id = :id; ^ robert@[local] postgres# \prompt 'what id? ' id what id? 123 robert@[local] postgres# select * from foo where id = :id; id ---- (0 rows)

There are no rows returned as I didn't add data for this example.

That would be a decent answer if you added some necessary explanations: for one, that you are using the command line client psql. A link to the documentation wouldn't hurt either.
Unfortunately we are not using command line. Ware using Pgadmin 4 so this will not work for us.. Is there any other possible way?
pgAdmin comes with psql built-in (the command-line thing that allows you to do \prompt), they call it PSQL tool. I don't think regular query editor in pgAdmin offers a feature like this but you can emulate it at the query/session level. There's also DBeaver and DataGrip you can switch to, both of which have that feature.

Collectives™ on Stack Overflow

Equivalent to replace input date from User/API In PostgreSQL from Oracle

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related