Postgres SELECT in Go returns all columns as string (using pq and database/sql)

Question

I'm doing a select in Go using the database/sql package and the pq Postgres driver:

rows, err := db.Query("SELECT (name, age) FROM people WHERE id = 1")

I tried retrieving the values in the normal way:

rows.Next()
name := ""
age := 0
err = rows.Scan(&name, &age)

but I got the error:

sql: expected 1 destination arguments in Scan, not 2

The documentation for sql.(*Rows).Scan says that you can pass a byte slice pointer and it will be filled with the raw results. So I did this:

b := make([]byte, 1024*1024)
rows.Scan(&b)
fmt.Println(string(b))

which succeeded, printing:

(John,18)

So I looked at the source code for sql.(*Rows).Scan, and it turns out that the error is returned if the number of arguments doesn't match the number of results returned by the database driver (which makes sense). So, for some reason, the pq driver seems to be returning the result set as a single value. Why would this be?

Remove the parentheses around the columns in the select list. SELECT (name, age) returns a single record (an anonymous composite type), but SELECT name, age will return two columns — user330315
– user330315, Commented Jan 6, 2014 at 22:45
@joshlf13 You'd probably need to look into the standard to find out, and it's "fun" reading. I think the standard supports it for things like UPDATE ... SET (a, b) = (x, y), and PostgreSQL has just generalized that by treating (a,b) as shorthand for ROW(a,b). — Craig Ringer
– Craig Ringer, Commented Jan 7, 2014 at 0:15
@a_horse_with_no_name : but what about if the query is calling a function which returns a query, such as RETURN QUERY SELECT arg1, arg2 removing parentheses doesn't work for functions — Joe
– Joe, Commented Sep 30, 2019 at 13:35
@Joe: no idea what you mean with that. If you then run select * from function() there is no need to remove any parentheses — user330315
– user330315, Commented Sep 30, 2019 at 14:00

joshlf · Accepted Answer · 2014-01-06 23:44:00Z

6

Thanks to a_horse_with_no_name for the right answer! I'm posting it here for ... posterity?

In Postgres, doing SELECT (a, b) with parentheses returns a single record, which is an anonymous composite type. Removing the parentheses will return the columns individually: Select a, b.

answered Jan 6, 2014 at 23:44

joshlf

23.9k15 gold badges80 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jabclab · Accepted Answer · 2015-02-27 16:23:03Z

2

When using a function with out parameters which only ever returns one row I was running into the same issue. The following resolved it for me:

var foo, bar string
err := db.QueryRow("select * from my_function()").Scan(&foo, &bar)

The function was of this form:

create or replace function my_function(
  out first_out varchar,
  out second_out json
) as $$
  -- etc.
$$ language plpgsql;

edited Feb 27, 2015 at 16:23

answered Feb 4, 2015 at 9:18

jabclab

15.1k5 gold badges57 silver badges51 bronze badges

Collectives™ on Stack Overflow

Postgres SELECT in Go returns all columns as string (using pq and database/sql)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related