Compare data postgresql

Question

I have got a database and a table inside it. Here my table:

Houses (
    id,
    doors,
    windows,
    bathrooms,
    wallColor,
    dateRent
)

I want to compare data os this table and get if it is equal or not: For example:

Select id, doors, windows, bathrroms, wallColor
from Houses
where dateRent='01-02-2013' equal(
    Select id, doors, windows, bathrroms, wallColor
    from Houses
    where dateRent='08-09-2014'
);

It just be great to give me true or false.

Are there several houses for each dateRent? – ffflabs Jul 09 '14 at 12:18 — ffflabs, Jul 09 '14 at 12:18

score 1 · Accepted Answer · answered Jul 09 '14 at 12:29

This will return the duplicated houses or no rows if none

select id, doors, windows, bathrroms, wallColor
from Houses
where dateRent in ('01-02-2013', '08-09-2014')
group by 1, 2, 3, 4, 5
having count(*) > 1

If you want to always return a single row with the true or false value:

with q as (
    select id, doors, windows, bathrroms, wallColor
    from Houses
    where dateRent in ('01-02-2013', '08-09-2014')
    group by 1, 2, 3, 4, 5
    having count(*) > 1
)
select exists (select 1 from q) as equal;

Depending on the server configuration you will need to use the ISO date format YYYY-MM-DD or another one.

BTW are you sure you want to include the id in the comparison? That looks like a primary key.

score 1 · Answer 2 · answered Jul 09 '14 at 12:30

I would do that with a join

SELECT DISTINCT houses1.id, coalesce(houses2.id, 'No Match') as Matched
FROM Houses h1
LEFT JOIN Houses h2 on h1.id=h2.id 
    AND h1.doors=h2.doors 
    AND h1.windows=h2.windows 
    AND h1.bathrooms=h2.bathrooms
    AND h1.wallColor=h2.wallColor 
    AND h2.dateRent='08-09-2014'
WHERE h1.dateRent='01-02-2013'

this will get you the house id of each house in the first date, and compare if there's at least a house in the second date with the same number of doors, windows, bathrooms and wallcolor

it won't return true or false, but instead the matching ID or 'No Match' if there isn't.

But if there can be more than one house per date, then a single true or false won't help you. You will always need the id field.

Compare data postgresql

2 Answers2