How to get all duplicate records in postgresql?

As I could get all duplicate records based on a column, for example I have the Code Table: 

╔════╦══════════╦══════════╗
║ id ║ codigo_1 ║ codigo_2 ║
╠════╬══════════╬══════════╣
║  1 ║ cod_abc  ║ cod_123  ║
╠════╬══════════╬══════════╣
║  2 ║ cod_xyz  ║ cod_234  ║
╠════╬══════════╬══════════╣
║  3 ║ cod_abc  ║ cod_345  ║
╠════╬══════════╬══════════╣
║  4 ║ cod_mno  ║ cod_456  ║
╠════╬══════════╬══════════╣
║  5 ║ cod_stv  ║ cod_567  ║
╠════╬══════════╬══════════╣
║  6 ║ cod_bcd  ║ cod_678  ║
╚════╩══════════╩══════════╝

I want to get all duplicate records from column codigo_1, for example the output of what I want would be: 

╔════╦══════════╦══════════╗
║ id ║ codigo_1 ║ codigo_2 ║
╠════╬══════════╬══════════╣
║  1 ║ cod_abc  ║ cod_123  ║
╠════╬══════════╬══════════╣
║  3 ║ cod_abc  ║ cod_345  ║
╚════╩══════════╩══════════╝

I have the following query: 

select * from codigo ou
where (select count(*) from codigo inr
where inr.codigo_1 = ou.codigo_1) > 1

But that query only returns me a duplicate record of each duplicate.

¿What consultation could I make to achieve what I want?

 1
Author: Juan Pinzón, 2016-08-24
Source

3 answers

You can use the command OVER: 

SELECT id, codigo_1, codigo_2
FROM (  SELECT  *, 
                COUNT(*) OVER(PARTITION BY codigo_1) N
        FROM codigo) as A
WHERE N > 1

Here you can find a demo with this code.

The results are: 

╔════╦══════════╦══════════╗
║ id ║ codigo_1 ║ codigo_2 ║
╠════╬══════════╬══════════╣
║  1 ║ cod_abc  ║ cod_123  ║
║  3 ║ cod_abc  ║ cod_345  ║
╚════╩══════════╩══════════╝
 4
Author: Lamak, 2016-08-24 17:33:10

This is:

  • facing the table columns against themselves
  • display pkey columns, and other relevant information
  • works fast 
Select tb1.pkey,tb1.column2,tb1.column3,tb2.pkey,tb2.column2,tb2.column3
  From tabla tb1, tabla tb2
  Where tb1.column2 = tb2.column2
  And   tb1.pkey <> tb2.pkey ;
 0
Author: ing.pedrojps, 2020-02-19 02:54:27 
SELECT primary_key, columna2, .._
FROM tabla
GROUP BY primary_key, columna2,..
HAVING (COUNT(*) > 1)
 -2
Author: Clay Guadalupe, 2019-03-21 00:01:12