Tables, Indexes and Triggers in Postgresql
List of table level commands and syntaxes in Postgres:
Sample Create table command with constraints:
mytestdb# create table mytable( empno integer primary key, firstname varchar(50), lastname varchar(50) );
Describe the table structure:
mytestdb# \d mytable << describes table showing all columns, datatypes, and constraints
select table_schema||'.'||table_name, column_name, data_type, is_nullable from information_schema.columns where table_schema='payments' and table_name='customer';
Create table with all constraints and serial no columns etc.,
customer_id serial primary key,
firstname varchar(50) not null,
lastname varchar(50) not null,
email varchar(50) unique not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
city varchar(30) not null,
state varchar(2) not null,
zip int not null,
create_date timestamp not null
);
customer_id INT not null, invoice_id INT NOT NULL, item_id INT NOT NULL, cost INT NOT NULL,
PRIMARY KEY (customer_id, invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id)
);
Alter table to add a column:
mytestdb# alter table mytable add date_joined timestamp;
Add a serial column to an existing table:
Note: This creates a sequence with name <tablename>_<columnname>_seq
mytestdb# alter table mytable add registerno serial;
Hint: You can also use IF NOT EXISTS during create table to create table only if it does not exist.
Lets look at creating indexes on the tables:
Sample Create an index on the postgres table:
mytestdb# create index myFirstIndex on mytable (registerno);
Check the query plan if it is using index or not:
EXPLAIN
select * from customer where customer_id =109;
Shows the below query plan:
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using customer_pkey on customer (cost=0.26..8.28 rows=1 width=696)
Index Cond: (customer_id = 109)
(2 rows)
Let us look at the creating triggers on the tables:
Two steps are involved in creating trigger.
1. create trigger function
2. create the trigger tagging it to the table
List indexes in postgres schema:
mytestdb# \di << list all indexes in the current schema
mytestdb# \di myschema. << list all indexes in the schema myschema
or
select schemaname||'.'||indexname, indexdef from pg_indexes where schemaname like 'myschema%';
Step1: Create the function for the trigger
CREATE OR REPLACE FUNCTION MyTrig_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
-- checks and actions as needed by the function
-- are to be written as part of the function body here
RETURN NEW;
END;
$$
Step 2: Create trigger on the table
CREATE TRIGGER myFirstTrigger
{before | after } {event}
on CUSTOMER
[FOR [EACH] { ROW | STATEMENT }]
EXECUTE PROCEDURE MyTrig_function
Example:
CREATE TRIGGER last_name_changes
BEFORE UPDATE
ON customers
FOR EACH ROW
EXECUTE PROCEDURE MyTrig_function();
Just for fun, here is one example of how to dump a bunch of rows (1000) into customer table for playing with the data:
mytestdb=# do $$
begin
for ct in 1..1000 loop
insert into customer values (ct, 'xx'||ct,'yy'||ct,'xx'||ct||'@yy.com','1 abcd st','1st floor','my city', 'ST', 12345, now());
end loop;
end; $$
mytestdb-# ;
DO
mytestdb=# explain
mytestdb-# select * from customer where customer_id=109;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using customer_pkey on customer (cost=0.26..8.28 rows=1 width=696)
Index Cond: (customer_id = 109)
(2 rows)
Comments
Post a Comment