PostgreSQL - Study Notes 2

 

     inet

 

 

 

 The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not. For example, 192.168. 0.1/24 is valid for inet but not for cidr .

 

Say, if you have a /8 netmask, the cidr type requires that all the 24 rightmost bits are zero. inet does not have this requirement.

db=# select '255.0.0.0/8'::cidr;
255.0.0.0/8

db=# select '255.1.0.0/8'::cidr;
ERROR:  invalid cidr value: "255.1.0.0/8"
DETAIL:  Value has bits set to right of mask.

And inet allows this:

db=# select '255.1.0.0/8'::inet;
255.1.0.0/8

     cidr

 

 

Prefer jsonb.

               

 

 

 

          @>

 

               unnest()

 

 

     split_part()

 

Text Search Vector

     to_tsvector()

Text Search Query

     to_tsquery(), @@

 

 

 

 

     bit

 

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)

 

     @>, &&

 

 

     upper_inc()

 

 

 

 

 

 

 

 

 

     &&

 

 

 

 

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 to 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.