Create a PostgreSQL constraint to prevent unique combination rows Imagine you have a simple table:

name | is_active
----------------
A    | 0
A    | 0
B    | 0
C    | 1
...  | ...


I need to create a special unique constraint which fails on following situation:
different is_active values can't co-exist for the same name value.

Example of permitted condition:

Note: simple multi-column unique index won't permit combination like this.

A    | 0
A    | 0
B    | 0


Example of permitted condition:

A    | 0
B    | 1


Example of failed condition:

A    | 0
A    | 1
-- should be prevented, because `A 0` exists
-- same name, but different `is_active`


Ideally, I need unique constraint or unique partial index. Triggers are more problematic for me.

Double A,0 allowed, but (A,0) (A,1) isn't.
You can use an exclusion constraint with btree_gist,

-- This is needed
CREATE EXTENSION btree_gist;


Then we add a constraint that says: 

"We can't have 2 rows that have the same name and different is_active":

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    EXCLUDE  USING gist
    ( name WITH =, 
      is_active WITH &lt;&gt;      -- if boolean, use instead:
                             -- (is_active::int) WITH &lt;&gt;
    );


Some notes:


is_active can be integer or boolean, makes no difference for the exclusion constraint. (actually it does, if the column is boolean you need to use (is_active::int) WITH &lt;&gt;.)
Rows where name or is_active is null will be ignored by the constraint and thus allowed.
The constraint makes sense only if the table has more columns. Otherwise, if the table has only these 2 columns, a UNIQUE constraint on (name) alone would be easier and more appropriate. I don't see any reason for storing multiple identical rows.
The design violates 2NF. While the exclusion constraint will save us from update anomalies, it may not from performance issues. If you have for example 1000 rows with name = 'A' and you want to to update is_active status from 0 to 3, all 1000 will have to be updated. You should examine whether normalizing the design would be more efficient. (Normalizing meaning in this case to remove is_active status from the table and add a 2-column table with name, is_active and a unique constraint on (name). If is_active is boolean, it could be totally stripped and the extra table just a single column table, storing only the "active" names.)