Postgresql – SQL Select query with postgresql

postgresql

I'd like to find all rows if is shows even once a particular value with postgresql

For example :

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
  3  M.        BOUVIER   Alain       V
  4  M.        DUBOIS    Paul        D
  5  M.        DREYFUS   Jean        C
  6  M.        FAURE     Alain       C
 11  M.        DUPONT    Alain       B
 12  M.        DUVAL     Arsène      B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

And I want to have all rows where CLI_ENSEIGNE = 'A' but not only I want also the rows where CLI_NOM had a CLI_ENSEIGNE = 'A' even only one time:

We see here the Dupond had an A the a B

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
 11  M.        DUPONT    Alain       B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

I tried

SELECT 
  *
FROM 
  table
WHERE 
CLI_ENSEIGNE = 'A' 
IN (CLI_NOM)

But it's saying that CLI_NOM doesn't exists

Best Answer

You where clause is incomplete and wouldn't work that way anyway.

You need a subselect for this:

SELECT   *
FROM the_table
WHERE cli_nom in (select cli_nom 
                  from the_table
                  where CLI_ENSEIGNE = 'A' );

alternatively a co-related sub-select with an exists clause:

SELECT t1.*
FROM the_table t1
WHERE EXISTS (select 42
              from the_table t2
              where t2.cli_nom = t1.cli_nom
                and CLI_ENSEIGNE = 'A' );

Related Solutions

PostgreSQL – Update Multiple Rows and Skip Row on Error

It seems there is a UNIQUE INDEX on table A(user_id) and you're trying to assign an existing value to more than one row.

Have a look at the next example:

create table a (id int, user_id int, f_id int);
create table b (id int, f_id int);
create unique index A_user_id_key on a(user_id);

insert into a 
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

insert into b
values (1, 11),
       (3, 22),
       (3, 33), --<<<< there is another row with user_id = 3
       (4, 44);

When I try to update using your current query:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;

It returns same error message:

ERROR: duplicate key value violates unique constraint "a_user_id_key"
DETAIL: Key (user_id)=(3) already exists.

You could solve by avoiding existing values:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);

This is the result:

select * from a;

id | user_id | f_id
-: | ------: | ---:
 1 |       1 |   11
 2 |       3 |   22
 3 |       2 |   33
 4 |       4 |   44

db<>fiddle here

But I'll suggest to check wich are the duplicate rows using next query:

select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id <> b.f_id);

id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33

db<>fiddle here

Postgresql – query on 2 fields but 1 is a unique value

I have extended a bit your sample data:

create table tbl (fld1 text, fld2 text);
insert into tbl values
('abc',  'D123'),
('xyz',  'D123'),
('abc',  'D125'),
('abc',  'D128'),
('def',  'D128'),
('abc',  'D789');

You can use EXIST in your WHERE clause with a dataset grouped by fld2:

select
    t1.*
from
    tbl t1
where
    fld1 = 'abc'
    and exists (select 1 
                from tbl t2 
                where t2.fld2 = t1.fld2 
                group by fld2 
                having count(*) = 1);

fld1 | fld2
:--- | :---
abc  | D125
abc  | D789

db<>fiddle here

Or you can use a CTE to get all fld2 rows with a count of 1, and join it with all rows where fld1 = 'abc':

with count1 as
(
    select   fld2
    from     tbl
    group by fld2
    having   count(*) = 1
)
select
    t1.*
from
    tbl t1
join
    count1
    on t1.fld2 = count1.fld2;

db<>fiddle here

Best Answer

Related Solutions

PostgreSQL – Update Multiple Rows and Skip Row on Error

Postgresql – query on 2 fields but 1 is a unique value

Related Question