Обсуждение: convert query from mysql

Please Note:

The information in this Business Wire e-mail message, and any files transmitted with it, is confidential and may be legally privileged. It is intended only for the use of the individual(s) named above. If you are the intended recipient, be aware that your use of any confidential or personal information may be restricted by state and federal privacy laws. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this e-mail message. If you have received this e-mail in error, please notify the sender and delete the material from any computer.

Mike Andrewjeski <Mike.Andrewjeski@businesswire.com> wrote:

> Here's the mysql query:

> select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
> node_group_memberships, nodes where nodes.id =
> node_group_memberships.node_id and node_groups.id =
> node_group_memberships.node_group_id and nodes.name IN (SELECT name
> from nodes) group by nodes.name order by nodes.name")
>
> What I'm having an issue with is the IN Clause.  Not sure how to
> do that in psql.

That query looks fine as it is if you just replace this:

  GROUP_CONCAT(node_groups.name)

with this:

  string_agg(node_groups.name, ',')

If that doesn't work, please show a test case where you provide SQL
code to create and populate the tables with minimal columns and
rows.  Show your query, explain what you think the results should
be, and show what you are getting instead.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Please Note:

The information in this Business Wire e-mail message, and any files transmitted with it, is confidential and may be legally privileged. It is intended only for the use of the individual(s) named above. If you are the intended recipient, be aware that your use of any confidential or personal information may be restricted by state and federal privacy laws. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this e-mail message. If you have received this e-mail in error, please notify the sender and delete the material from any computer.

On 18/12/13 10:55, Mike Andrewjeski wrote:
> [...]
> > Here's the mysql query:
>
> > select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
> > node_group_memberships, nodes where nodes.id =
> > node_group_memberships.node_id and node_groups.id =
> > node_group_memberships.node_group_id and nodes.name IN (SELECT name
> > from nodes) group by nodes.name order by nodes.name")
[...]

Just noticed the IN clause...

The semantics of how NULLS are handled is different, but you might find
it more efficient if you don't use IN, but something like:

(SELECT n2.name FROM nodes n2 WHERE n1.name = n2.name)

I always uses aliases for tables, especially when I'm dealing with more
than one table in an SQL statement.


Cheers,
Gavin

On 18/12/13 11:08, Gavin Flower wrote:
> On 18/12/13 10:55, Mike Andrewjeski wrote:
>> [...]
>> > Here's the mysql query:
>>
>> > select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
>> > node_group_memberships, nodes where nodes.id =
>> > node_group_memberships.node_id and node_groups.id =
>> > node_group_memberships.node_group_id and nodes.name IN (SELECT name
>> > from nodes) group by nodes.name order by nodes.name")
> [...]
>
> Just noticed the IN clause...
>
> The semantics of how NULLS are handled is different, but you might
> find it more efficient if you don't use IN, but something like:
>
> (SELECT n2.name FROM nodes n2 WHERE n1.name = n2.name)
>
> I always uses aliases for tables, especially when I'm dealing with
> more than one table in an SQL statement.
>
>
> Cheers,
> Gavin
>
>
Arghhhhhhhh!

Of course within seconds of hitting enter, I realized my brain was not
fully engaged!

EXISTS (SELECT 1 FROM nodes n2 WHERE n1.name = n2.name)

might even work!


Cheers,
Gavin

On Tue, Dec 17, 2013 at 3:44 PM, Mike Andrewjeski
<Mike.Andrewjeski@businesswire.com> wrote:
>
> Hi all,
>
> psql newbie here. I'm working to convert a mysql query written in ruby to
> using postgres as our database is now postgres 9.2
>
> Here's the mysql query:
>
> select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
> node_group_memberships, nodes where nodes.id =
> node_group_memberships.node_id and node_groups.id =
> node_group_memberships.node_group_id and nodes.name IN (SELECT name
> from nodes) group by nodes.name order by nodes.name")
> What I'm having an issue with is the IN Clause.  Not sure how to do that in
> psql.
>

Hi,

I was looking at this and i admit i don't understand how that IN could
ever be false (which could be caused by the beer i just drank).
The reason i don't understand it is that you are checking if the names
in table nodes exists in the table nodes :S


--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

On 4 Jan 2014 13:09, "Jaime Casanova" <jaime@2ndquadrant.com> wrote:
>
> On Tue, Dec 17, 2013 at 3:44 PM, Mike Andrewjeski
> <Mike.Andrewjeski@businesswire.com> wrote:
> >
> > Hi all,
> >
> > psql newbie here. I'm working to convert a mysql query written in ruby to
> > using postgres as our database is now postgres 9.2
> >
> > Here's the mysql query:
> >
> > select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
> > node_group_memberships, nodes where nodes.id =
> > node_group_memberships.node_id and node_groups.id =
> > node_group_memberships.node_group_id and nodes.name IN (SELECT name
> > from nodes) group by nodes.name order by nodes.name")
> > What I'm having an issue with is the IN Clause.  Not sure how to do that in
> > psql.
> >
>
> Hi,
>
> I was looking at this and i admit i don't understand how that IN could
> ever be false (which could be caused by the beer i just drank).

Lets not blame beer :-)

> The reason i don't understand it is that you are checking if the names
> in table nodes exists in the table nodes :S

Agree! It unneccessarily increases the cost of query.
I am not well versed with My SQL. But if it supports SQL properly you would not need that IN. But if that IN was added cause of some 'syntax/planner defficiency' in MySQl, you can safely drop it in PostgreSQL.
BTW what is the issue you are having in PostgreSQL? Error or unexpected result?