CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Vittal Cherala
Vittal Cherala

Reputation: 477

how to get distinct records from all columns from table in postgresql

I have a table with 100 columns and i need to get distinct records from all the columns from the table. I used below query to get distinct records from table

select distinct col1, col2, col3,........ from test_table

but is there any good query to fetch distinct records from all the columns from table without mentioning column names in the query.

Upvotes: 0

Views: 1264

Answers (2)

404
404

Reputation: 8582

Since you want DISTINCT on all columns, and you want to select all columns, it couldn't be simpler:

SELECT DISTINCT * FROM table

Upvotes: 1

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

I am not sure if there is a simpler way, You can use information_schema to get your columns and then use it.

    SELECT string_agg(column_name::character varying, ',') as columns
FROM information_schema.columns 
WHERE table_schema = 'schema_name'
AND table_name   = 'table_name'

This will return you the list of columns in your table. 

 SELECT string_agg(column_name::character varying, ',') as columns
    FROM information_schema.columns 
    WHERE table_schema = 'schema_name'
    AND table_name   = 'table_name' \gset

You can refer to gset here,

For example, if your table has two columns 'a' and 'b', gset will store, 'a,b'.

echo might be used to check what gset has stored,

\echo :columns

The following query might help you, 

select distinct :columns from table_name;

Upvotes: 0

Related Questions