Temporary tables in POSTGRESQL
Why and How do we create temporary table ?
Temporary option is to create a copy from a table already present in the database , these tables are not committed and hence get deleted when the session is closed .
Lets say we create a table called Shoes with columns type and size .
now we create a temporary table called sandals from shoes :
using:
>>create temporary table sandals as (select * from shoes where shoe_type = 'sandals');
--this will get only the shoe_type equal to sandals in sandals.
--this will help simplify more complicated queries .
--we prefer temporary tables as they are faster to operate on.
>>insert into sandals values('juta',11);
>>delete from sandals where shoe_type = 'juta';
Note
single line comments --
multi line comments /* */
Limiting Results:
use Limit after from statement to limit the number of outputs if table is huge.
>>select prod_name from Products LIMIT 5; --SQL
>>select prod_name from Products where ROWNUM<=5; --Oracle
>>select prod_name from Products fetch first 5 rows only; --DB2
Relational vs. Transactional Model
relational - Allows for easy querying and data manipulation in an easy, logical and intuitive way whereas in transactional data is stored at runtime without any grouping and its not good for querying , the data is usually moved from transactional database to the relational database.
Temporary option is to create a copy from a table already present in the database , these tables are not committed and hence get deleted when the session is closed .
Lets say we create a table called Shoes with columns type and size .
now we create a temporary table called sandals from shoes :
using:
>>create temporary table sandals as (select * from shoes where shoe_type = 'sandals');
--this will get only the shoe_type equal to sandals in sandals.
--this will help simplify more complicated queries .
--we prefer temporary tables as they are faster to operate on.
>>insert into sandals values('juta',11);
>>delete from sandals where shoe_type = 'juta';
Note
single line comments --
multi line comments /* */
Limiting Results:
use Limit after from statement to limit the number of outputs if table is huge.
>>select prod_name from Products LIMIT 5; --SQL
>>select prod_name from Products where ROWNUM<=5; --Oracle
>>select prod_name from Products fetch first 5 rows only; --DB2
Relational vs. Transactional Model
relational - Allows for easy querying and data manipulation in an easy, logical and intuitive way whereas in transactional data is stored at runtime without any grouping and its not good for querying , the data is usually moved from transactional database to the relational database.
I encourage you to check out and review one or more of these resources.
Comments
Post a Comment