Create table with distinct column values from existing table in sql-CodePudding

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.

CodePudding user response：

As per my understanding, You simply need a DISTINCT clause -

CREATE TABLE customers as
SELECT DISTINCT customer_code, market_code, market_name, zone 
  FROM transactions;

CodePudding user response：

Hi add DISTINCT in your Query and group all columns and save this as csv. PostgreSQL Import CSV later.

    SELECT DISTINCT 
       customer_code -- 1
      ,market_code   -- 2
      ,market_name   -- 3
      ,zone          -- 4

   FROM transactions 
   GROUP by 1,2,3,4