How to create new table in PostgreSQL from existing table that has columns with distinct row values?

Question

I have table tansactions that looks like this -

I want to make a separate customers table that has distinct 'customer_code' in ascending order and related 'market_code','market_name', and 'zone' columns. the resultant table would look like this -

I have tried -

create table customers as (
select customer_code, market_code, market_name, zone 
from transactions group by customer_code);

This works fine on MySQL workbench but doesn't work on PGadmin.

Answer 1

In order for the customer_code field to have unique values ​​in this table, the other fields cannot have different values ​​in the transactions table for the same customer_code, for example, if it is possible for a customer_code to have different values ​​in the market_code, market_name or zone fields, in different rows of the transactions table so this calls for a clustering strategy that you haven't considered.

The GROUP BY clause requires some aggregation function like SUM, MAX, MIN, AVG, etc... in MySQL when you don't use any of that it brings the first row found ensuring the grouping of what was requested in the GROUP BY , in PostgreSQL this does not happen, the aggregation function is required on ungrouped fields.

Below I give an example of how to work breaking up tables without creating views or something else, which makes it easier for complex issues like your question. Maybe if you are more specific in the question it will be easier to give a better answer.

CREATE TABLE customers AS 
WITH STEP_A AS (
    SELECT 
        T.customer_code
        , COUNT(T.customer_code) AS TOT
    FROM
        transactions T 
    GROUP BY
        T.customer_code 
)
, STEP_B AS (
    SELECT 
        A.customer_code 
    FROM
        STEP_A A
    INNER JOIN 
        transactions T 
        ON A.customer_code = T.customer_code 
    WHERE 
        A.TOT = 1
)

SELECT 
    B.* 
FROM 
    STEP_B B 
ORDER BY
    B.customer_code ASC
;

Hope this helps!! Good luck!