PostgreSQL - Study Notes 2

 

 

 

 

 

 

 

Prefer jsonb.

               

 

 

 

     

 

          

 

 

 

Text Search Vector

Text Search Query

 

 

 

 

 

zzhtest=> select B'0101'   -- user's feature flags
               & B'0001'   -- mask: if the result equals the mask, the user has that feature
----------
 0001

 

create table bits (
    bit3 bit(3),
    bitv bit varying(32)  -- up to 32 bits
);

 

select '[1,5]'::int4range;
-- [1,6)
-- 5.99 is not valid

select '[1,5]'::numrange;
-- [1,5]
-- 5.99 is valid but not included

select '[1,6)'::int4range;
-- [1,6)
-- 5.99 is not valid

select '[1,6)'::numrange;
-- [1,6)
-- 5.99 is valid and included

 

 

 

select numrange(1, 5);
-- [1,5)

select int4range(1, 5);
-- [1,5)

select numrange(1, 5, '[]');
-- [1,5]

select numrange(1, 5, '(]');
-- (1,5]

select int2range(1, 5, '[]');
ERROR:  function int2range(integer, integer, unknown) does not exist
LINE 1: select int2range(1, 5, '[]');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

select int4range(1, 5, '[]');
-- [1,6)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The difference between 'no action' and 'restrict' is very sutle.'no action' allows the check to be deferred to later in a transaction whereas 'restrict' does not allow that check to be deferred in later in a transaction, but at the end of the day, the result is the same, you can not delete the parent row without first deleting the child row, but you can change that by same CASCADE.