Search Postgresql Archives

Find duplicates in a column then print Information to a file

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Development Platform: Ubuntu 17.10 mainly command line work

Tools: perl 5.26 and postgresql 9.6

Goal: Display duplicate aria titles on screen and to a local file

Database name: arias

Table name: aria_precis

csv delimiter: the # symbol

arias=# \d aria_precis
   Table "public.aria_precis"
   Column    | Type | Modifiers
-------------+------+-----------
 id          | text | not null
 aria        | text |
 artist      | text |
 a_artist    | text |
 album_title | text |
Indexes:
    "aria_precis_pkey" PRIMARY KEY, btree (id)

Problems:
  1. Can't connect aria title to id
  2. Can't write discovered information to file

I know about this link but I probably didn't understand what I read there.
https://www.postgresql.org/docs/current/static/sql-select.html#SQL-WITH

What I know displays the 46 duplicate aria titles of 413 entries but not the id or artist:
SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis GROUP BY aria  HAVING COUNT(aria)>1);

When I perform the following I get (0 rows):
SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis GROUP BY id, aria  HAVING COUNT(aria)>1);
 aria
------
(0 rows)

After I get the above information how do I write that information to a file? The only thing I know writes the entire database to a file:
 \COPY aria_precis TO '/home/sherman/aria_precis_2.csv' WITH DELIMITER '#,' CSV HEADER;

Thank you;

Sherman

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux