Restricting the value of columns in PostgreSQL

ENUM
--Enum type
 --case sensitive
 --4 Bytes on disk
 CREATE TYPE vaildcolor AS ENUM ('Blue','Pink');

REGEXP

--Case insensitivie regexp
 --accepts NULL
 --useful for complex/long text matching
 ALTER TABLE color ADD CONSTRAINT
 vaild_color CHECK(
 color ~*
 '^(blue|pink)$'
 );

CHECK

 --Case sensitivie =
 --accepts NULL
 ALTER TABLE color ADD CONSTRAINT
 vaild_color CHECK(
 color = 'Blue' OR color = 'Pink'
 );
Advertisements

Registros repetidos

Algunos queries útiles para lidiar con con registros repetidos.

Encuentra lor registros repetidos junto con el número de veces que están repetidos.

SELECT DISTINCT(column_id), count(column_id)
    FROM table_name
    GROUP BY column_id
    HAVING (count(column_id)>1)

Para borrar los repetidos:

DELETE FROM table_name where rowid NOT IN
 (SELECT max(rowid) FROM table GROUP BY duplicate_values_field_name);

Una alternativa para borrar

DELETE  duplicate_values_field_name dv FROM table_name ta 
WHERE rowid <(SELECT min(rowid)  FROM table_name tb 
WHERE ta.dv=tb.dv);