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 <fola...@peoplecall.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 > <operasopra...@gmail.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. >
