#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo