If I query without the "exists" clause I get the PG exception that the
relation does not exist (which I guess tells me the table does not
exist) but I'd like a better way to test for existence than
exceptions.
On Sun, Jun 10, 2018 at 4:15 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>
> On 10 June 2018 at 19:09, Chuck Davis <cjgunzel@gmail.com> wrote:
>>
>> If I try to return a ResultSet the class will not compile with the
>> message that boolean cannot be converted to a ResultSet. And this is
>> in synch with the documentation that states the "exists" subquery will
>> return a boolean -- not a ResultSet.
>>
>> There are a number of sites that indicate the statement is the way to
>> find out if a table exists. Are the sites for an older version of PG?
>> I'm running on 10.
>>
>> Thanks.
>>
>> On Sun, Jun 10, 2018 at 1:44 PM, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > On Sunday, June 10, 2018, Chuck Davis <cjgunzel@gmail.com> wrote:
>> >>
>> >>
>> >> try {
>> >> result = stat.execute("select exists (select 1 from
>> >> information_schema.tables where table_name = 'entities')");
>> >> System.out.println("the checkEntity returned a result of "
>> >> + result);
>> >> } catch (SQLException ex) {
>> >>
>> >> Logger.getLogger(ClientConstants.class.getName()).log(Level.SEVERE,
>> >> null, ex);
>> >> result = false;
>> >> return result;
>> >> }
>> >
>> >
>> > Your query is putting "false" into cell (0,0) of a ResultSet. You are
>> > failing to even look at the resultset to see if it holds a true or
>> > false.
>> >
>> > The query should never fail since you aren't using the table name
>> > directly
>> > but are checking for it as a value in another table that always exists.
>> > The
>> > inner query returns zero records when the table doesn't exist and the
>> > EXISTS
>> > construct converts that to false.
>> >
>> > David J.
>> >
>>
>
>
> idiomatic java suggests that you need to do
>
> resultset rs = statement.query(your select statement)
>
> if rs.next then
> result = rs.getInt(1)
>
> close result set and statement
>
> return result == 1
>