Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can use a <code>having</code> clause to get the list of entries that are duplicated:</p> <pre><code>SELECT abc.entry FROM table abc JOIN table1 def ON abc.entry = def.entry WHERE def.created &gt;= 'some_date' AND def.position = 1 group by abc.entry having count(*) &gt; 1 </code></pre> <p>To get the list of rows, I would use window functions:</p> <pre><code>select t.* from (SELECT abc.*, count(*) over (partition by entry) as cnt FROM table abc JOIN table1 def ON abc.entry = def.entry WHERE def.created &gt;= 'some_date' AND def.position = 1 ) t where cnt &gt; 1; </code></pre> <p>EDIT:</p> <p>I see the confusion over the terminology. <code>DISTINCT</code> has a very particular meaning in SQL. And, <code>DISTINCT ON</code> has a rather different meaning in Postgres. I think you have to do this with a subquery:</p> <pre><code>select abc.* from table abc left outer join (SELECT DISTINCT ON (abc.entry) abc.* FROM table abc JOIN table1 def ON abc.entry = def.entry WHERE def.created &gt;= 'some_date' AND def.position = 1 ) f on abc.id = f.id where f.id is NULL </code></pre> <p>A word of caution, though. You are using <code>distinct on</code> without an <code>order by</code> clause. The particular row being returned in the data is indeterminate. You should add an <code>order by</code> to your original data and to the subquery here.</p> <pre><code>select abc.* from table abc left outer join (SELECT abc.entry, max(abc.id) as maxid FROM table abc JOIN table1 def ON abc.entry = def.entry WHERE def.created &gt;= 'some_date' AND def.position = 1 group by abc.entry ) f on abc.id = f.maxid where f.maxid is NULL </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPostgres: Get the Complement of Distinct Entries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGordon Linoff
    1. USGordon Linoff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSorry, neither of these quite gets me there. The first query returns a list of entry, which I already have from my initial query. If you correct the second query to use "abc.entry" on line 2, it returns a list of all lines where duplicates occur. What I really need is the complement. If I have sets {{1, A}, {1, B}, {2, A}, {2, B}, {3, A}, {4, A}, ...}, and I perform my initial query, I might get {{1,A}, {2,B}, {3,A}, {4,A}. If I can find the IDs of {{1,B}, {2,A}}, those are the records, I want to delete. I can't have them all mixed together.
      singulars
      1. PO
      1. USDarin Peterson
    2. CO@DarinPeterson . . . Do you have an `id` column in `abc`?
      singulars
      1. PO
      1. USGordon Linoff
    3. COYes, def has an id column, abc has an id column as well as the foreign key to def.id. "entry" is the key to def in my initial query.
      singulars
      1. PO
      1. USDarin Peterson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload