Pg: How can I get true or false from the database?

[rhymes]

I'm looking at the API but I'm not sure how to get "False" from the database

> SELECT EXISTS(SELECT 1 FROM apps WHERE value = 'fake value');
+----------+
| exists   |
|----------|
| False    |
+----------+

I tried using:

result, err := s.DB.ExecOne("SELECT EXISTS(SELECT 1 FROM apps WHERE value = ?0)", value)
if err != nil {
    return false, err
}
return result.RowsReturned() > 0, nil

in a function but it always returns true obviously because there's always a row returned.

How do I get the actual result?

Thanks

[rhymes]

Solved it like this :-)

var exists bool
err := s.DB.Model(&nm.App{}).
    ColumnExpr("EXISTS(SELECT 1 FROM apps WHERE value = ?0)", value).
    Select(&exists)
if err != nil {
    return false, err
}
return exists, nil

[vmihailenco]

SELECT count(*) looks like a more natural solution. Overall this does not look like a go-pg issue :)

[rhymes]

@vmihailenco I believe "EXISTS" is a bit more efficient. If you think about it: why asks the DB to count if you're not interested in the result? For more reference: https://dzone.com/articles/avoid-using-count-in-sql-when-you-could-usenbspexi

But you're right, it's not an issue, I just had troubles finding the solution in the doc.

Thanks!

[23doors]

In case someone stumbles upon it (like I did), here is a neat utility function that performs exists on given query.

// Exists performs SELECT EXIST on given query.
func Exists(q *orm.Query) (bool, error) {
    var exists bool
    return exists, q.New().ColumnExpr("EXISTS(?)", q.ColumnExpr("1")).Select(&exists)
}

Usage:
Exists(db.Model(...).Where(...))

Maybe we could add it to go-pg as a orm.Query method?

[vmihailenco]

I don't mind having something like exists, err := q.Model(...).Where(...).Exists() in orm.Query. As for the implementation I would go with something like:

func (q *orm.Query) Exists() (bool, error) {
    cp := q.Copy() // copy to not change original query
    cp.columns = []string{"true"}
    cp.order = nil
    cp.limit = 1
    var exists bool
    err := cp.Select(&exists)
    return exists, err
}

That should generate query like SELECT true FROM table WHERE ... LIMIT 1 which should be as efficient as SELECT EXISTS, but easier/faster for go-pg to generate the query.

@23doors do you want to send a PR? :)