I'm trying to get this column structure:

| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |

My table strucrure looks like this

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(id) NOT NULL,
    name VARCHAR NOT NULL,
    main_image CHAR(60) NULL,
    rating float(2) DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE shops_and_products (
    shop_id INT REFERENCES shops(id) NOT NULL,
    product_id INT REFERENCES products(id) NOT NULL,
    cost INT NOT NULL,
    url VARCHAR NOT NULL
);

CREATE TABLE products_and_characteristics (
    product_id INT REFERENCES products(id) NOT NULL,
    characteristic_id INT REFERENCES characteristics(id) NOT NULL,
    value VARCHAR NOT NULL
);

So far my query look like this:

SELECT "pr"."id", "pr"."name", "pr"."main_image", "pr"."rating", 
        array_agg(sh.name) AS "shop_names", 
        array_agg(snp.cost) AS "prices", 
        array_agg(snp.url) AS "urls",
        "2", "3"
FROM "products" AS "pr" 
LEFT JOIN "shops_and_products" AS "snp" ON ("snp"."product_id" = "pr"."id")
LEFT JOIN "shops" AS "sh" ON ("sh"."id" = "snp"."shop_id")
LEFT JOIN crosstab('SELECT product_id, characteristic_id, value 
                    FROM products_and_characteristics
                    WHERE characteristic_id IN (2,3)
                    ORDER BY characteristic_id') 
          AS ct ("pr_id" int, "2" varchar, "3" varchar) 
          ON ("pr_id" = "pr"."id")
WHERE (("pr"."category_id" = 1) AND ("pr"."id" > -1))
GROUP BY "pr"."id"
ORDER BY "pr"."popularity" DESC

But I get this error while runnning it:

column "ct.2" must appear in the GROUP BY clause or be used in an aggregate function

Which is very odd to me, since there aren't supposed to be any arrays. How can I change my query to acomplish my goal?

I'm trying to get this column structure:

| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |

My table structure looks like this

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(id) NOT NULL,
    name VARCHAR NOT NULL,
    main_image CHAR(60) NULL,
    rating float(2) DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE shops_and_products (
    shop_id INT REFERENCES shops(id) NOT NULL,
    product_id INT REFERENCES products(id) NOT NULL,
    cost INT NOT NULL,
    url VARCHAR NOT NULL
);

CREATE TABLE products_and_characteristics (
    product_id INT REFERENCES products(id) NOT NULL,
    characteristic_id INT REFERENCES characteristics(id) NOT NULL,
    value VARCHAR NOT NULL
);

So far my query look like this:

SELECT "pr"."id", "pr"."name", "pr"."main_image", "pr"."rating", 
        array_agg(sh.name) AS "shop_names", 
        array_agg(snp.cost) AS "prices", 
        array_agg(snp.url) AS "urls",
        "2", "3"
FROM "products" AS "pr" 
LEFT JOIN "shops_and_products" AS "snp" ON ("snp"."product_id" = "pr"."id")
LEFT JOIN "shops" AS "sh" ON ("sh"."id" = "snp"."shop_id")
LEFT JOIN crosstab('SELECT product_id, characteristic_id, value 
                    FROM products_and_characteristics
                    WHERE characteristic_id IN (2,3)
                    ORDER BY characteristic_id') 
          AS ct ("pr_id" int, "2" varchar, "3" varchar) 
          ON ("pr_id" = "pr"."id")
WHERE (("pr"."category_id" = 1) AND ("pr"."id" > -1))
GROUP BY "pr"."id"
ORDER BY "pr"."popularity" DESC

But I get this error while runnning it:

column "ct.2" must appear in the GROUP BY clause or be used in an aggregate function

Which is very odd to me, since there aren't supposed to be any arrays. How can I change my query to acomplish my goal?

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(id) NOT NULL,
    name VARCHAR NOT NULL,
    description VARCHAR NULL,
    main_image CHAR(60) NULL,
    updated TIMESTAMP NOT NULL,
    rating float(2) DEFAULT 0.0 NOT NULL,
    popularity REAL DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    description VARCHAR NULL,
    url VARCHAR NOT NULL,
    main_image CHAR(60),
    rating float(2) DEFAULT 0.0 NOT NULL,
    popularity REAL DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops_and_products (
    shop_id INT REFERENCES shops(id) NOT NULL,
    product_id INT REFERENCES products(id) NOT NULL,
    cost INT NOT NULL,
    url VARCHAR NOT NULL
);

CREATE TABLE products_and_characteristics (
    product_id INT REFERENCES products(id) NOT NULL,
    characteristic_id INT REFERENCES characteristics(id) NOT NULL,
    value VARCHAR NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(id) NOT NULL,
    name VARCHAR NOT NULL,
    main_image CHAR(60) NULL,
    rating float(2) DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE shops_and_products (
    shop_id INT REFERENCES shops(id) NOT NULL,
    product_id INT REFERENCES products(id) NOT NULL,
    cost INT NOT NULL,
    url VARCHAR NOT NULL
);

CREATE TABLE products_and_characteristics (
    product_id INT REFERENCES products(id) NOT NULL,
    characteristic_id INT REFERENCES characteristics(id) NOT NULL,
    value VARCHAR NOT NULL
);