Some Important Query
1)create table ram(id serial,name text,age int);
2)insert into ram (name,age) values ('monish','2');
3)delete from ram where id='3';
4)alter table drupal_mst_vendor add column "ipaddress" inet;
5)alter table drupal_mst_vendor add column "date_of_updation" date;
6)alter table drupal_mst_vendor add column "emp_id" charater varying;
7)SELECT * FROM drupal_users where state_code='33' and employee_code is not null // <----for not null
8)SELECT * FROM drupal_users where state_code='33' and employee_code is null // <---- for null
9)SELECT pk_sub_category_code,pk_main_category_code,sub_category_name FROM drupal_mst_sub_category group by sub_category_name,pk_main_category_code,pk_sub_category_code order by pk_main_category_code
10)UPDATE Store_Information SET Sales = 500 WHERE store_name = "Los Angeles" AND Date = "Jan-08-1999"
// -------------command used for checking the duplicate records of a particular column---------
11)select pk_machine_serial_no from drupal_item_details group by pk_machine_serial_no having count(*)>'1'
12)select cast(date_of_install as date) from drupal_item_details.
13)select cast(DATEFIELD as date) from ram
14)select value,(case when (value ='null') then 'UnAuthorised' else 'Authorised' end) as notreg
from drupal_profile_values where fid='35'
15)select * from drupal_role where name like '%Rights'
16)select * from drupal_txn_audit_track where date_of_operation::date='2010-12-21'
17)SELECT * from edist_upload LIMIT '2' OFFSET '2'
18)select * from drupal_txn_item_master where date_of_install!=''
limit '5' offset '1'
19)select substring(state_name,1) as ram from drupal_mst_states
20)select substring(cast(pk_state_code as text),1) as ram from drupal_mst_states
******************************************************************************
For Creating and Altering Sequence
CREATE SEQUENCE drupal_database_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE drupal_database_seq OWNER TO drupaluser;
// Alter sequence cmd for restarting it with 1
alter sequence bseq restart with 1;
the code to be entered in default value if a sequence is created to user interface
nextval(('sequence_name')::text)::regclass
*********************************************************************************
Changing Data type of columns
ALTER COLUMN column_name TYPE
new_data_type USING column_name::new_data_type;
*********************************************************************************
Terminating time from timestamp of a date
-->select call_booking_date from drupal_txn_call_book_report
output: "2011-05-10 12:08:20"
==> select call_booking_date::date from drupal_txn_call_book_report
output: "2011-05-10"
*********************************************************************************
Extracting day, month or year from a given date in postgres
select extract(year from published_date) from information_table;
output: 2012
