CREATE VIEW WITH CHECK OPTION in PostgreSql

CREATE VIEW working fine with PostgreSql but CREATE VIEW WITH CHECK OPTION would show error message like WITH CHECK OPTION is not implemented in postgres

In PostgreSql we don't have "create view .. with check option", we could do the same in alternative way by creating rule.

create rule

I) for insert

CREATE OR REPLACE RULE rule_name AS ON INSERT TO view_name
DO INSTEAD
INSERT INTO table_name VALUES(id=new.id,name=new.name)

II) for Update

CREATE OR REPLACE RULE rule_name AS ON UPDATE TO view_name
DO INSTEAD
UPDATE table_name SET name=new.name WHERE id=new.id

III) for Delete

CREATE OR REPLACE RULE rule_name AS ON DELETE TO view_name
DO INSTEAD
DELETE FROM table_name WHERE id=new.id

example

you are having table 'students ' with 'id' and 'name' column

create view vw_students as select * from students

create rule for up-datable view

CREATE OR REPLACE RULE update_vw AS ON UPDATE TO vw_students
DO INSTEAD
UPDATE students SET name=new.name WHERE id=new.id

now you can able update the view by

update vw_students set name='postgres' where id=1

note : parent table also get updated whenever you update on view

ILIKE keyword in PostgreSql

ILIKE keyword in PostgreSQL provides case insensitive  search.

PostgreSql provides case sensitive as well as case insensitive pattern matching, traditional LIKE operator do the same as other rdbms, ILILKE operator used for case insensitive pattern matching.

ex : SELECT name FROM employee WHERE name LIKE 'AN%'

query will display only ANTRO, ANBU, ANTUN etc..

ex : SELECT name FROM employee WHERE name ILIKE 'AN%'

query will display name start with 'an' irrespective case, ANTRO, antTro, ANBU,AnTUN, ANTUN etc..

===================================================

Issue :: DATE data type with pattern matching operator (LIKE) in version 8.3 later.

Date data type with LIKE operator works fine for the prior to 8.3 version.

for ex : SELECT name FROM employee WHERE date_of_birth LIKE '2007-01-19%'; // older version works fine

query will display all the name of date_of_birth falls on 2007-01-19.

but version 8.3 will gives hint like
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

and
This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to inappropriate behaviours. Prior to 8.3, your timestamps was
casts to text implicitly.

to get the same query output you may use "date_trunc" function its works like normal "trunc" function.

for ex : SELECT name FROM employee WHERE date_trunc('day',date_of_birth) = '2007-01-19';
query will round with date and display all the name.

note : instead of field ' day ' in date_trunc function you may use

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

function would round according to the field value.

TOP n query in PostgreSQL

SQL Server provides TOP clause to return top n row from query,

for ex : SELECT TOP 5 * FROM Tbl_Name order by col_name

in PostgreSql we have LIMIT clause limit the number of rows returned,

for ex : SELECT * FROM Tbl_Name ORDER BY col_name LIMIT 5

PostgreSql and MySql also provides cool feature with LIMIT clause called OFFSET

for ex : SELECT * FROM Tbl_Name ORDER BY col_name LIMIT 5 OFFSET 20

it will skip the row from 1 to 19 and return the record from 20th to 25th record.