Some problem during conversion from MySql to PosgreSql

Dev Shed Forums Sponsor:

June 13th, 2012, 04:20 AM

apocalys

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 19 m 5 sec
Reputation Power: 0

Some problem during conversion from MySql to PosgreSql

Hi all,

I need to convert this mysql script into posgresql script but any time I give some error. Can anyone help me?

Code:

SELECT  
		DL1.`refId_user` AS `search_refId_user`, 
		DL2.`refId_user`, 
		U.`name`, U.`surname`, 
		D.`desc` AS `discussion_desc`, 
		D.`id` AS `refId_discussion`
FROM 	
		(SELECT * FROM `DISTRIBUTION_LIST` WHERE `refId_user` = 121 GROUP BY `refId_discussion` ORDER BY `id` DESC LIMIT 10) DL1,
		`DISTRIBUTION_LIST` DL2,
		`USERS` U,
		`DISCUSSIONS` D
WHERE
		DL1.`refId_discussion` = DL2.`refId_discussion` AND
		DL2.`refId_user` = U.`id` AND
		DL2.`refId_discussion` = D.`id`
ORDER BY 
		D.`id` DESC, 
		DL2.`id` DESC;

tkx to all!

June 13th, 2012, 05:59 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 6 sec
Reputation Power: 284

Remove those invalid backticks. That's not valid SQL

Depending on how you created the tables and columns you will probably need to replace them with double quotes:

so

DL1.`refId_user` AS `search_refId_user`,

has to be

DL1."refId_user" AS "search_refId_user",

The next time please also post the error message.

June 13th, 2012, 07:13 AM

apocalys

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 19 m 5 sec
Reputation Power: 0

I correct all backticks:

Code:

SELECT  
		"DL1"."refId_user" AS "search_refId_user", 
		"DL2"."refId_user", 
		"U"."name", "U"."surname", 
		"D"."desc" AS "discussion_desc", 
		"D"."id" AS "refId_discussion"
FROM 	
		(SELECT * FROM "DISTRIBUTION_LIST" WHERE "refId_user" = 121 GROUP BY "refId_discussion" ORDER BY "id" DESC LIMIT 10) "DL1",
		"DISTRIBUTION_LIST" "DL2",
		"USERS" "U",
		"DISCUSSIONS" "D"
WHERE
		"DL1"."refId_discussion" = "DL2"."refId_discussion" AND
		"DL2"."refId_user" = "U"."id" AND
		"DL2"."refId_discussion" = "D"."id"
ORDER BY 
		"D"."id" DESC, 
		"DL2"."id" DESC;

I get the following error:

Code:

ERROR:  column "DISTRIBUTION_LIST.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 8:   (SELECT * FROM "DISTRIBUTION_LIST" WHERE "refId_user" = 12...

If I correct the script in this way

PHP Code:


		
			
 SELECT  

        "DL1"."refId_user" AS "search_refId_user", 

        "DL2"."refId_user", 

        "U"."name", "U"."surname", 

        "D"."desc" AS "discussion_desc", 

        "D"."id" AS "refId_discussion"

FROM     

        (SELECT * FROM "DISTRIBUTION_LIST" WHERE "refId_user" = 121 GROUP BY "refId_discussion", "id" ORDER BY "id" DESC LIMIT 10) "DL1",

        "DISTRIBUTION_LIST" "DL2",

        "USERS" "U",

        "DISCUSSIONS" "D"

WHERE

        "DL1"."refId_discussion" = "DL2"."refId_discussion" AND

        "DL2"."refId_user" = "U"."id" AND

        "DL2"."refId_discussion" = "D"."id"

ORDER BY 

        "D"."id" DESC, 

        "DL2"."id" DESC;

it is correct and work but the result that i get is not correct cause that the group by for "refId_discussion" is not working.

How i can fix the script?

Tkx to all

June 13th, 2012, 07:26 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 6 sec
Reputation Power: 284

All columns that are not used in an aggregate column must appear in the group by clause (as the error message says). MySQL incorrect behaviour returned some arbitrary (read random) rows from the group when not following that rule.

But as you are not using any aggregates in your statement, the group by does not make any sense anyway. What is it you are trying to achieve?

June 13th, 2012, 08:15 AM

apocalys

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 19 m 5 sec
Reputation Power: 0

I have to extract data from this schema:

Table Discussion (id)
Table Messages (id)
Table Distribution_list (id, discussionId, messageId, userId)
Table User (id)

User1 send a message, that belongs to a discussion, to User2:
system add into Distribution_list this 2 row ({discussionId, messageId, user1}, {discussionId, messageId, user2}).

I need to get all user's discussion, with all participant.

I hope to had explain well my situation.

Some problem during conversion from MySql to PosgreSql

Developer Shed Advertisers and Affiliates