GO PQ driver cannot query with default schema

Question

Code to open connection to DB:

DB, err = sql.Open("postgres", "dbname=dev user=postgres password=postgres port=5400 sslmode=disable")

I'm trying to do a simple select query like:

err := DB.QueryRow("SELECT id FROM user WHERE id = $1", id)

but I get the error: pq: column id does not exist

However, if I try with the schema:

err := DB.QueryRow("SELECT id FROM public.user WHERE id = $1", id)

it goes fine. I don't understand why I must write the schema even though the default schema is public in the postgresql I'm running on Docker

Answer 1

user is a reserved keyword of postgres that is used to get information about the current database user. When you run a query like SELECT * FROM user, it retrieves information about the current database user, not the data from a table named user.

But with SELECT id FROM public.user this, it will get the result from user table of public schema. This ensures that PostgreSQL looks specifically in the public schema for the user table and retrieves the desired data.

Solutions

1 - Rename your table into users

2 - Or use the schema name along with the query.

References :

• SQL Key Words

Answer 2

check the default search_path with this SQL :

SHOW search_path;

if it is not public then set it:

ALTER ROLE your_user SET search_path = public;