Wednesday 13 July 2016

postgreSQL Queries

PostgreSQL Queries syntax:

 Data types:
character varying(n), varchar(n) --> variable-length with limit
character(n), char(n) --> fixed-length
text --> variable unlimited length
integer or int --> this type is for integer, and also we have few variants for bigger and smaller integers(ie smallint,bigint)
timestamp --> time and date
time --> only time(ie HH:MM:SS)
date --> only date

PostgreSQL queries syntaxs are mostly like oracle
insert Query:
Syntax1: to insert values into paticular columns
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Syntax2:to insert into all columns
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

select Query:
SELECT column1, column2, columnN FROM table_name;

update Query:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

delete Query:
DELETE FROM table_name
WHERE [condition];

alter Query:
Few Examples:
ALTER TABLE EMP ADD COLUMN address varchar(30);
ALTER TABLE EMP DROP COLUMN address;
ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);


drop Query:
DROP TABLE table_name;

like and ilike:
SELECT FROM table_name
WHERE column LIKE 'XXXX';

ILIKE is to ignorecase
SELECT FROM table_name
WHERE column ILIKE 'XXXX';

Wildcards % and _ can be used in search string.

limit clause:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

order by clause:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

group by clause:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

having clause:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN


also see below
what is postgreSQL
Why postgresSQL
History of PostgreSQL
postgreSQL - PSQL MATA Commands
postgreSQL what is schema?
postgreSQL Connecting to a Database
postgreSQL Create and access Database
How to create user in postgreSQL?
postgreSQL Grant/Revoke Privileges
postgreSQL Tablespaces and datafiles
postgreSQL How to start server and stop server?
postgreSQL perfomance
postgreSQL How to create cluster?
postgreSQL Queries
postgreSQL routine maintenance and proactive-maintanace.
postgresql-logging
at No comments:
Labels:

postgresql logging

below properties can be updated in postgresql.conf file

Where To Log

  • log_destination-Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.
  • logging_collector-Enables advanced logging features. csvlogrequires logging_collector.
  • log_directory-Directory where log files are written. Requires logging_collector.
  • log_filename-Format of log file name (e.g. enterprisedb-%Y-%M-%d-%h%m%s.log). Allows regular log rotation. Requires logging_collector.
  • log_file_mode(default 0600) -On Unix systems this parameter sets the permissions for log files when logging_collectoris enabled.
  • log_rotation_age-Automatically rotate logs after this much time. Requires logging_collector.
  • log_rotation_size-Automatically rotate logs when they get this big. Requires logging_collector.


When To Log
  • client_min_messages(default NOTICE) -Messages of this severity level or above are sent to the client.
  • log_min_messages(default WARNING) -Messages of this severity level or above are sent to the server.
  • log_min_error_statement(default ERROR) -When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it.
  • log_min_duration_statement(default -1, disabled) -When a statement runs for at least this long, it is written to the server log, with its duration.


What To Log
  • log_connections(default off) -Log successful connections to the server log.
  • log_disconnections(default off) -Log some information each time a session disconnects, including the duration of the session.
  • log_error_verbosity(default “default”) -Can also select “terse” or “verbose”.
  • log_duration(default off) -Log duration of each statement.
  • log_line_prefix-Additional details to log with each line.
  • log_statement(default none) -Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all.
  • log_temp_files(default -1) -Log temporary files of this size or larger, in kilobytes.
  • log_checkpoints(default off) -Causes checkpoints and restartpointsto be logged in the server log.
at No comments:
Labels:

postgreSQL routine maintenance and proactive-maintanance

1)Analyze:

syntax: analyze <table-name>
==>Which will updates statistics (system_tables) (or) That autovaccum will update after configured time,Then only we can see updated statistics.

2)Analyze verbose:
syntax:analyze verbose <table-name>
==> shows number of pages and Number of live records(usually deleted rows wont get cleaned up immediately once we call Analyze then only dead rows will cleaned up).

3)vacuum:
Sysntax: Vacuum <table-name>
This will removes dead rows. Main use of vacuum is delete dead row which will increase performance.
How to view hidden(deleted) fields of all tables.
Ex: select c+id,* from emp;
output:
c+id      id
(0,1)      1
(0,2)      2
-----
(0,255) 255
(1,1)   256
Note: If we remove dead rows from  table  we can achieve "good performance".

4)Vacuum full:
vacuum full will gives returns disk space to disk.
Note: In PostgerSQL each row will have trasaction Id:
How to view transcation id of each row of table?
Answer: select xmin,c+id,* from emp;

5)Vacuum freeze:
Under one transaction we can inserts 4 milion.If we insert above 4 milions in this case 4 milion are inserted then very first one will replaced with last row(4 milion +1).
To over come above issue vacuum freeze will be run by autovacuum for every 2 million records.

6)Vacuum analyze:
7)Explain:
It will give only plan time.This won't execute query explain select * from emp;
cost=0.00 .... 1.10 rows=10 width=4
show cpu-tuple-cost;
show seq-page-cost;

8)Explain analyze:
This will execute query.Explain analyze will give planning time and execution time.

9)Autovacuum:
PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands.


Data loading:
=========

EDB loader is using to import CSV data.
copy from------>import
copy To--------->Export

Examlpe:
1)copy emp(id,name) to /temp/emp.csv csv Header;
2)copy id-test from 'temp/emp.csv' csv Header;
==>csv is in binary format since this will be 95% faster than insert.
also see below
at No comments:
Labels:

postgreSQL How to create cluster?

PostgreSQL Cluster is nothing but another instance of Database.
Syntax:
initDB -D <direcoty>

Example:
step 1: create directory for table.
    mkdir mynewtable-space

step 2: initDB -D /mynewtable-space

at No comments:
Labels:

How to increase Query Performance

How to get fastness on query? How to get results quickly?
By inceasing work_mem we can acheive fastness on query results
show work_mem;
set work_mem to 1000MB

Increasing work mem to specific user
Alter user <user> set work_mem to '1000MB'

How to increase read performance?

By making below changes in config file we can achieve better read performance

fsynce=false
increase work_mem
increase shared_bufferes
create indexs

How to increase write performance?

By making below changes in config file we can achieve better write performance

decrease checkpoint_timeout
increase shared_buffers
increase wal_buffers
increase work_mem
at No comments:
Labels:

postgreSQL How to start server and stop server?

How to stop postgreSQL?

./pg_ctl -D ../data stop

How to force stop postgreSQL?
./pg_ctl -D ../data stop -m f --->force stop

How to start postgreSQL?
./pg_ctl -D ../data start

How to restart postgreSQL?
./pg_ctl -D ../data restart

Note: In above samples pg_ctl is a executable which can be found under bin directory. -D stands for directory and ../data is data directory.


Security :
pg_hdb.cfg configuration file will have all clients IP address non listed clients cannot access.
pg_hba.conf can be find under data directory not under bin directory.another config file postgres.cfg where will do postgreSQL configurations.

How to load configuration file without restart with query?
Answer: select pg_reload_conf();

How to rotate db logs?
Answer: select pg_rotate_logfile();

How to view log file location?
Answer: select pg_ls_dir('pg_log');

How to view system tables?
Answer: \d$
also see below

at 2 comments:
Labels:

postgreSQL Tablespaces and datafiles

  • Each database has a separate subdirectory.
  • Tablespaces can be created using the CREATE TABLESPACE command.
  • The Tablespace directory must be created and the appropriate permissions must be set prior to running the CREATE TABLESPACE command.

Syntax:
    CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'

  Use tablespace:

  Use the TABLESPACE keyword while creating tables and indexes to specify a   tablespace name.
  Syntax: 
            TABLESPACE <table space name>
  • default_tablespace server parameter sets default tablespace.
  • default_tablespace parameter can also be set using the SET command at the session level.temp_tablespaces parameter determines the placement of temporary tables and indexes and temporary files.
  • temp_tablespaces can be a list of tablespace names.
Example:
show default_tablespace;
show temp_tablespaces;
How to set default table space?

  • set default_tablespace <table-space>
  • Alter tablespace name rename to new_name
  • Alter tablespace name owenr to new_owner
 Drop table space:
drop tablespace <name>

create database in different table space:
create databse <db-name> tablespace="<table-space-name>
create database <db-name> <table-space-name>

create table space with location:
at No comments:
Labels:

postgreSQL Grant OR Revoke user Privileges

Cluster level:

  • Granted to a user during CREATE or later using ALTER USER
    These privileges are granted by superuser

Object Level:

  • Granted to user using GRANTcommand
  • These privileges allow a user to perform particular action on an database object, such as table, view, sequence etc.
  • Can be granted by Owner, superuser or someone who have been given permission to grant privilege (WITH GRANT OPTION)

Grant statement:

  • GRANT can be used for granting object level privileges to database users, groups or roles.
  • Privileges can be granted on a tablespace, database, schema, table, sequence, domain and function.
  • GRANT is also used to grant a role to a user.
Syntax:
-Type \h GRANT in edb-psqlterminal to view the entire syntax and available privileges that can be granted on different objects


Grant commad

Revoke statement:

  • REVOKE can be used for revoking object level privileges to database users, groups or roles.
  • Privileges can be revoked on a tablespace, database, schema, table, sequence, domain and function.
  • REVOKE [ GRANT OPTION FOR ] can be used to revoke only the grant option without revoking the actual privilege.
at No comments:
Labels:

How to create user in postgreSQL?

  • Database users are completely separate from operating system users.
  • Database users are global across a database cluster.
  • Username must be unique and require authentication.
  • Every connection made to a database is made using a user.
  • enterprisedbis a predefined superuserin default data cluster.
  • Pre-defined superusername can be specified during initialization of the database cluster.
  • This pre-defined superuserhave all the privileges with GRANT OPTION.
create user thourg commad
The create user utility can also be used to create a user
Syntax:
create user[OPTION]... [ROLENAME]
  • Use --help option to view the full list of options available.
Ex:  create user --help
also see below
at No comments:
Labels:

postgreSQL Create and access Database

Create Database:

Createdb is a program(command) that executes from the shell to create new databases.
Example: createdb edbstore

  • Create Database command can be used to create a database in a cluster.
Syntax:
Creating Databases
CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace][ CONNECTION LIMIT [=] connlimit ] ]

Access Database:

The psqltool allows you to interactively enter, edit, and execute SQL commands.
The PEM Client tool can also be used to access a database.
To use psql to access a database:
    -Open the command prompt or terminal.
    -If PATH is not set you can execute next command from the bin directory location of              postgresinstallation

at No comments:
Labels:

postgreSQL Connecting to a Database

at No comments:
Labels:

postgreSQL what is schema?

Schema is nothing but user ownes 
  • Tables
  • Views
  • Sequences
  • Functions
  • Domains
  • Packages
  • Procedures
  • Synonyms
  • Database Links
  • Procedures

Benefits of Schems:

  • A database can contains one or more named sachems.
  • By default, all databases contain a public schema.
There are several reasons why one might want to use sachems:
  • To allow many users to use one database without interfering with each other.
  • To organize database objects into logical groups to make them more manageable.
  • Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Schema search path:

  • Qualified names are tedious to write, so we use table names directly in queries.
  • If no schema name is given, the schema search path determines which schemas are searched for matching table names
Example:
SELECT * FROM employee
This statement will find the first employee table from the schemas listed in the search path.

  • The first schema named in the search path is called the current schema if that named schema exists.
  • Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.
  • To show the current search path, use the following command:
            SHOW search_path;
  • To put our new schema in the path, we use:
        SET search_path TO myschema, public;
  • The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.
  • The second element refers to the public schema that we have seen already. To put our new schema in the path, we use:

SET search_path TO myschema, public;

Default Schema:
postgres=# show search_path;
search_path
----------------
"$user",public
(1 row)
postgres=#

To put our new schema in the path, we use:
SET search_path TO myschema, public;

Example:

postgres=# set search_path TO myschema,public;
SET
postgres=# create table cust(id integer, name varchar(10));
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------------------+----------+----------
myschema | cust | table | postgres
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(3 rows)
postgres=# set search_path to "$user",public;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(2 rows)
also see below
at No comments:
Labels:
Subscribe to: Posts (Atom)