11

Following code is working fine. But I want to define array['a', 'b', 'c', 'd', 'e'] as a variable. 

rows, err :=  db.Query("select colname from (SELECT date, unnest(array['a', 'b', 'c', 'd', 'e']) AS colname, unnest(array[a, b, c, d, e]) AS thing from test1 where date='123') as tester where thing=1;")

So I try following code using github.com/lib/pq .

arr1 := []string{"a", "b", "c", "d", "e"}      
rows, err :=  db.Query("select colname from (SELECT date, unnest($1) AS colname, unnest($1) AS thing from test1 where date='123') as tester where thing=1;", pq.Array(arr1))

But getting error like "pq: function unnest(unknown) is not unique". Table structure and sample data--

test=# \d+ test1
                                Table "public.test1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 a      | character varying(10) |           | extended |              | 
 b      | character varying(10) |           | extended |              | 
 c      | character varying(10) |           | extended |              | 
 d      | character varying(10) |           | extended |              | 
 e      | character varying(10) |           | extended |              | 
 date   | character varying(10) |           | extended |              | 

test=# select * from test1 ;
 a | b | c | d | e | date 
---+---+---+---+---+------
 3 | 1 | 3 | 2 | 3 | 124
 3 | 3 | 2 | 2 | 1 | 125
 1 | 2 | 2 | 1 | 3 | 126
 1 | 2 | 3 | 2 | 3 | 127
 1 | 1 | 2 | 2 | 3 | 123
(5 rows)

Basically I want the column name (a,b,c,d or e) which have value '1' on any specific date.

govind prajapati
  • 113
  • 1
  • 6
  • Possible duplicate of [PostgreSQL 9.3: Function is not unique error](https://stackoverflow.com/questions/34431315/postgresql-9-3-function-is-not-unique-error) – Jonathan Hall Oct 06 '18 at 11:10
  • Not expert in `postgresql` but in c# with standard sql i would create array of characters, then merge them with separator ", " and then use it as parameter. – Aleksa Ristic Oct 06 '18 at 11:33
  • @AleksaRistic PostgreSQL has array types (which AFAIK are standard SQL even though hardly anyone supports them). – mu is too short Oct 06 '18 at 19:56

1 Answers1

22

I'd guess that pq.Array is giving you a PostgreSQL array in the string form so you end up with something like this:

unnest('{a,b,c,d,e}')

and PostgreSQL isn't sure how it should interpret that string, hence the complaint about unnest(unknown). You should be able to add an explicit type cast to clear things up:

unnest($1::text[])         -- PostgreSQL-specific casting syntax
unnest(cast($1 as text[])) -- Standard casting syntax

You'd end up with something like this:

rows, err :=  db.Query("select colname from (SELECT date, unnest($1::text[]) AS colname, unnest($1) AS thing from test1 where date='123') as tester where thing=1;", pq.Array(arr1))
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • There is something wrong happened when variable substitute in 2nd unnest. When I uses rows, err := db.Query("select colname from (SELECT date, unnest($1::text[]) AS colname, unnest(array[a, b, c, d, e]) AS thing from test1 where date='123') as tester where thing='1';", pq.Array(arr1)) It is giving right output .But when uses following code I found no error and no output. rows, err := db.Query("select colname from (SELECT date, unnest($1::text[]) AS colname, unnest($1) AS thing from test1 where date='123') as tester where thing=1;", pq.Array(arr1)) – govind prajapati Nov 12 '18 at 07:54
  • What happens if you try the query in `psql`? – mu is too short Nov 12 '18 at 08:42
  • test=# select colname from (SELECT date, unnest(array['a', 'b', 'c', 'd', 'e']) AS colname, unnest(array[a, b, c, d, e]) AS thing from test1 where date='123') as tester where thing='1'; colname --------- a b (2 rows) – govind prajapati Nov 12 '18 at 09:02
  • It gives 2 outputs a and b. – govind prajapati Nov 12 '18 at 09:04
  • You're going to have to include the table structure and some sample data in your question. – mu is too short Nov 12 '18 at 20:35
  • Updated the description – govind prajapati Nov 13 '18 at 03:29
  • I'd recommend asking a new question with those new details and a description of you're trying to get your query to do, tag it with "sql" and "postgresql". You have an XY-problem and you're trying to solve the wrong problem (you want to solve "how do I get my desired results" rather than "how do I make *this* query work"). – mu is too short Nov 13 '18 at 23:34
  • Ok I have created a new question . Thanks for help. – govind prajapati Nov 14 '18 at 02:43