Re: Find duplicates in a column then print Information to a file

Keep in mind there is a quick write-to-file in psql with ‘\o <filename>’. And don’t forget to turn it off with ‘\o’

> On Dec 31, 2017, at 12:04 PM, Sherman Willden <operasopranos(at)gmail(dot)com> wrote:
>
> Thank you for the replies. I will start working on them now. Not a student but since I now have the time I may look into it. I am 71 retired working at Home Depot. I have a collection of CDs by various artists and I have the time to create and maintain my own database concerning these subjects. I retired from USAF, SUN Microsystems, and HP where I worked with the UNIX systems. As I said my main question right now is which duplicate songs do I have and who performs them. I really appreciate the support I get here.
>
> Sherman
>
>> On Sun, Dec 31, 2017 at 11:53 AM, Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
>> Mmmmm, I notice a faint homework smell here ;-> , but the question is
>> nicely asked so:
>>
>> On Sun, Dec 31, 2017 at 7:19 PM, Sherman Willden
>> <operasopranos(at)gmail(dot)com> wrote:
>> ...
>> > SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis
>> > GROUP BY aria HAVING COUNT(aria)>1);
>>
>> The outer select is fully redundant. Just use the inner one. In fact,
>> not redundnat, harmful, as it will spit many copies of aria for the
>> duplicated ones. If you add id (and/or artist) to the outer query then
>> it is useful again.
>>
>>
>> > 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);
>>
>> As expected. See the inner query, you are grouping by ID which is the
>> primary key, by PK definition all counts are going to be one, so no
>> results, nothing goes to the outer query.
>>
>> Use the first query, but adding id and artist to the OUTER level.
>>
>> > 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;
>>
>> If copy format is ok to you, knowing \copy look at the docs, looking
>> at https://www.postgresql.org/docs/9.6/static/app-psql.html you will
>> read:
>>
>> >>>
>> \copy { table [ ( column_list ) ] | ( query ) } { from | to } {
>> 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [
>> [ with ] ( option [, ...] ) ]
>>
>> Performs a frontend (client) copy. This is an operation that runs an
>> SQL COPY command, but instead of the server reading or writing the
>> specified file, psql reads or writes the file and routes the data
>> between the server and the local file system. This means that file
>> accessibility and privileges are those of the local user, not the
>> server, and no SQL superuser privileges are required.
>> <<<
>>
>> Notice the (query) option? Your copy is using the table+optional
>> column list format, (aria_precis), just change it to the "( query )"
>> format ( NOTICE THE MANDATORY PARENS, this is how copy knows what to
>> do. "t" => table, "t(c1)"=>table+columns, "(xx)" => query ( nothing
>> before the opening parens ). )
>>
>> Happy new year.
>>
>> Francisco Olarte.
>