"does not exist" errors for simple joins #213

Activity

1. kyleconroy commented on Dec 24, 2019

   

   kyleconroy

   on Dec 24, 2019

   Last edited by kyleconroy

   CollaboratorAuthor

   More actions

   @tgascoigne I'm going to assume that the foo and bar tables look like this

   CREATE TABLE foo (
     id integer primary key
   );
   
   CREATE TABLE bar (
     id integer references foo(id),
     title text
   );
2. tgascoigne commented on Dec 25, 2019

   

   tgascoigne

   on Dec 25, 2019

   More actions

   Yep, that's equivalent to what I'm using
3. kyleconroy commented on Dec 25, 2019

   

   kyleconroy

   on Dec 25, 2019

   CollaboratorAuthor

   More actions

   There are two issues at play. The first is that sqlc doesn't currently support SELECT foo FROM foo. This is the reason the first two queries error. Do you have example Go code you use to scan those results?

   The third query fails due to incorrect alias support, and should be easier to fix.
4. kyleconroy

   added

   bugSomething isn't workingSomething isn't working

   on Dec 25, 2019
5. tgascoigne commented on Dec 25, 2019

   

   tgascoigne

   on Dec 25, 2019

   More actions

   Thanks for the quick response :)

   Unfortunately I don't have much more than the examples provided and the code generated by sqlc - I've just started playing around with it today for potential use in future.

   My hope was that I'd get something like the below struct generated, but I'd be happy to have any way to extract all columns from both tables:

   type SomeRow struct {
       Foo Foo
       Bar Bar
   }

   In the meantime I've worked around it by explicitly selecting each field I care about, which works perfectly fine:

   SELECT foo.id, bar.title
   FROM foo 
   JOIN bar ON bar.id = foo.id
   WHERE foo.id = $1;
6. kyleconroy commented on Dec 25, 2019

   

   kyleconroy

   on Dec 25, 2019

   CollaboratorAuthor

   More actions

   but I'd be happy to have any way to extract all columns from both tables

   You can get that behavior by using *:

   SELECT *
   FROM foo 
   JOIN bar ON bar.id = foo.id
   WHERE foo.id = $1;

   Selecting the table name (SELECT foo FROM foo) returns a single column with a composite type ROW(id). Composite types have poor support in lib/pq. The value passed to Scan is tuple represented as a string (e.g. (id)). I haven't found a library that properly handles these values. SELECT * has much better support.
7. tgascoigne commented on Dec 25, 2019

   

   tgascoigne

   on Dec 25, 2019

   More actions

   Now that you mention it I did try SELECT *, but both tables have an id field, and sqlc expands the * out to something like SELECT id, title, id (which postgres rejects due to ambiguity)
8. kyleconroy commented on Dec 26, 2019

   

   kyleconroy

   on Dec 26, 2019

   CollaboratorAuthor

   More actions

   which postgres rejects due to ambiguity

   🤦‍♂ yeah, * expansion should be smart enough to generate columns that do not collide.
9. kyleconroy

   added

   📚 postgresql

   on Feb 10, 2020
10. kyleconroy

    closed this as completedin #326on Feb 10, 2020
11. kyleconroy commented on Feb 10, 2020

    

    kyleconroy

    on Feb 10, 2020

    CollaboratorAuthor

    More actions

    @tgascoigne This has now be fixed in master. The following two queries should now work

    SELECT f.id, b.title
    FROM foo f
    JOIN bar b ON b.id = f.id
    WHERE f.id = $1;
    
    SELECT *
    FROM foo 
    JOIN bar ON bar.id = foo.id
    WHERE foo.id = $1;