Query with schema.table fails

[jpmeijers]

I have a query like this: rows, err := db.Query("SELECT * FROM \"community\".\"community\"")

The query fails with this message: pq: relation "community.community" does not exist

It looks like the sql/pq library strips quotes from my query and adds new quotes - incorrectly.

If this doesn't work, what is the correct way to query and join tables between different "schemas".

Example:

SELECT * FROM device.deviceportcommunity dev
JOIN community.community com ON dev.deviceportcommunityid = com.communityid

This example works find in other SQL clients and libraries, but fails with the above error in this library.

[johto]

The query fails with this message: pq: relation "community.community" does not exist

It looks like the sql/pq library strips quotes from my query and adds new quotes - incorrectly.

This library does no such thing. You'll notice that the error messages makes these two cases indistinguishable:

=# select * from "community"."community";
ERROR:  relation "community.community" does not exist
LINE 1: select * from "community"."community";

=# select * from "community.community";
ERROR:  relation "community.community" does not exist
LINE 1: select * from "community.community";

So your problem is elsewhere, but it's impossible to guess given the information here.

[jpmeijers]

As I was typing and proofreading the reply below, I found the error.

My DSN:

    dsn := "host=" + credentials.host +
        " port=" + strconv.Itoa(credentials.port) +
        " user=" + credentials.username +
        " dbname=" + credentials.username +
        " password=" + credentials.password +
        " sslmode=disable"

is using the username as the database name. That will definitely not work. Fixing this makes the query execute correctly.

Thanks for the quick response @johto and making me look again more carefully at my code.

If you have any further comments on my code, please let me know how I can improve.

---

This library does no such thing.

Indeed. I'm looking at the Postgres query log now and I see the query is passed on exactly as I typed it (I removed the double quotes now).

psql command line works:

timescale_1  | 2021-06-16 12:53:44.201 UTC [231] LOG:  statement: SELECT * FROM community.community;

ExpressJS works:

timescale_1  | 2021-06-16 12:47:28.838 UTC [156] LOG:  statement: SELECT * FROM community.community

Datagrip also works:

timescale_1  | 2021-06-16 12:48:05.080 UTC [40] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
timescale_1  | 2021-06-16 12:48:05.288 UTC [40] LOG:  execute <unnamed>: select current_database() as a, current_schemas(false) as b
timescale_1  | 2021-06-16 12:48:05.496 UTC [40] LOG:  execute <unnamed>: SELECT * FROM community.community
timescale_1  | 2021-06-16 12:48:07.448 UTC [40] LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL

Golang fails:

timescale_1  | 2021-06-16 12:48:51.053 UTC [174] LOG:  statement: ;
timescale_1  | 2021-06-16 12:48:51.257 UTC [174] LOG:  statement: SELECT * FROM community.community
timescale_1  | 2021-06-16 12:48:51.257 UTC [174] ERROR:  relation "community.community" does not exist at character 15
timescale_1  | 2021-06-16 12:48:51.257 UTC [174] STATEMENT:  SELECT * FROM community.community

So your problem is elsewhere,

Yes. But the queries that are received on Postgres' side are exactly the same, except Datagrip that wraps it in a transaction. Another difference I see is that lib/pq executes an empty query before the real query.

but it's impossible to guess given the information here.

Yes indeed. I'm also not sure where to find any additional info to debug this. Please let me know if you have any ideas.

My database code looks like this:

package main

import (
    "database/sql"
    _ "github.com/lib/pq"
    "log"
    "strconv"
)

var (
    db *sql.DB
)

type DbCredentials struct {
    host string
    port int
    username string
    password string
    database string
}

func (credentials *DbCredentials) InitDb() {
    dsn := "host=" + credentials.host +
        " port=" + strconv.Itoa(credentials.port) +
        " user=" + credentials.username +
        " dbname=" + credentials.username +
        " password=" + credentials.password +
        " sslmode=disable"
    var err error
    db, err = sql.Open("postgres", dsn)
    if err != nil {
        log.Fatalln(err)
    }

    err = db.Ping()
    if err != nil {
        panic(err)
    }
}

func GetCommunities(user int) (*sql.Rows, error) {
    rows, err := db.Query("SELECT * FROM community.community")
    if err != nil {
        return nil, err
    }
    return rows, nil
}

And the code is called like this:

package main

import (
    "log"
    "testing"
)

func TestGetCommunities(t *testing.T) {
    credentials := DbCredentials{
        host: REDACTED,
        port: 5432,
        username: "postgres",
        password: REDACTED,
        database: REDACTED,
    }
    credentials.InitDb()

    communities, err := GetCommunities(1211)
    if err != nil {
        log.Println(err.Error())
    }
    log.Printf("%+v", communities)

    communities, err = GetCommunities(1211)
    if err != nil {
        log.Println(err.Error())
    }
    log.Printf("%+v", communities)
}

In the above test I am calling GetCommunities twice to see when the empty statement is executed. We can see in the log below it happens only once, ie. at connect.

timescale_1  | 2021-06-16 12:58:30.620 UTC [294] LOG:  statement: ;
timescale_1  | 2021-06-16 12:58:30.812 UTC [294] LOG:  statement: SELECT * FROM community.community
timescale_1  | 2021-06-16 12:58:30.813 UTC [294] ERROR:  relation "community.community" does not exist at character 15
timescale_1  | 2021-06-16 12:58:30.813 UTC [294] STATEMENT:  SELECT * FROM community.community
timescale_1  | 2021-06-16 12:58:31.048 UTC [294] LOG:  statement: SELECT * FROM community.community
timescale_1  | 2021-06-16 12:58:31.049 UTC [294] ERROR:  relation "community.community" does not exist at character 15
timescale_1  | 2021-06-16 12:58:31.049 UTC [294] STATEMENT:  SELECT * FROM community.community

[johto]

" dbname=" + credentials.username +

Copy-paste-o?

[jpmeijers]

I blame my IDE's auto completion of variable names.