Daily *Nix

User Tools

Site Tools

Trace:
resources

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

resources [2011/11/15 15:40]
k2patel [Admin SQL] 		resources [2020/08/10 02:35]
Line 1: Line 1:
-====== PostgreSQL ====== 
- 
-==== Common Commands ==== 
-<code sql> 
-List of Databases : 
-psql -l 
-\? Help 
-\d ~= desc 
- 
-\c <​database>​ -- select database 
- 
-\dt Show Tables 
-\dT list datatypes 
-\df list functions 
-\di list indexes 
-\dv list views 
- 
- 
-  \da [PATTERN] ​ list aggregate functions 
-  \db [PATTERN] ​ list tablespaces (add "​+"​ for more detail) 
-  \dc [PATTERN] ​ list conversions 
-  \dC            list casts 
-  \dd [PATTERN] ​ show comment for object 
-  \dD [PATTERN] ​ list domains 
-  \df [PATTERN] ​ list functions (add "​+"​ for more detail) 
-  \dg [PATTERN] ​ list groups 
-  \dn [PATTERN] ​ list schemas (add "​+"​ for more detail) 
-  \do [NAME] ​    list operators 
-  \dl            list large objects, same as \lo_list 
-  \dp [PATTERN] ​ list table, view, and sequence access privileges 
-  \dT [PATTERN] ​ list data types (add "​+"​ for more detail) 
-  \du [PATTERN] ​ list users 
-  \l             list all databases (add "​+"​ for more detail) 
-  \z [PATTERN] ​  list table, view, and sequence access privileges (same as \dp) 
-</​code>​ 
- 
-==== Admin SQL ==== 
-== Create User == 
-<code sql> 
-CREATE ROLE k2patel LOGIN PASSWORD '​test'​ VALID UNTIL '​infinity';​ 
-</​code>​ 
- 
-== Create Table Space == 
-<code sql> 
-CREATE TABLESPACE k2patel OWNER k2patel LOCATION '/​var/​lib/​pgsql/​k2patel';​ 
-</​code>​ 
- 
-== Create Database == 
-<code sql> 
-CREATE DATABASE k2patel WITH OWNER = k2patel ENCODING = '​UTF8'​ TABLESPACE = k2patel LC_COLLATE = '​en_US.UTF-8'​ LC_CTYPE = '​en_US.UTF-8'​ CONNECTION LIMIT = -1; 
-</​code>​ 
- 
-== List Current Activity == 
-<code sql> 
-select * from pg_stat_activity;​ 
-</​code>​ 
- 
-== Change Pasword == 
-<code sql> 
-ALTER USER postgres WITH ENCRYPTED PASSWORD '​password';​ 
-</​code>​ 
- 
-== Read Only User == 
-This only allow user to access database but can not write to it. 
- 
-<code sql> 
-CREATE ROLE carol; 
-ALTER ROLE carol WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ENCRYPTED PASSWORD '​password';​ 
-GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol; 
-</​code>​ 
- 
-==== Getting Connection Information ==== 
- 
-<code sql> 
-select inet_server_addr();​ 
-</​code>​ 
- 
-Following is the available procedures / variable. 
- 
- 
-{| style="​border-spacing:​0;"​ 
-! <​center>​Name</​center>​ 
-! <​center>​Return Type</​center>​ 
-! <​center>​Description</​center>​ 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_catalog</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| name of current database (called&​nbsp;"​catalog"&​nbsp;​in the SQL standard) 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_database()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| name of current database 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_schema<​nowiki>​[()]</​nowiki></​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| name of current schema 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_schemas(boolean)</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt><​nowiki>​name[]</​nowiki></​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| names of schemas in search path, optionally including implicit schemas 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_user</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| user name of current execution context 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​current_query()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​text</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| text of the currently executing query, as submitted by the client (might contain more than one statement) 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_backend_pid()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​int</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| Process ID of the server process attached to the current session 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_listening_channels()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​setof text</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| channel names that the session is currently listening on 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet_client_addr()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| address of the remote connection 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet_client_port()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​int</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| port of the remote connection 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet_server_addr()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| address of the local connection 
- 
-|- 
-| style="​background-color:#​efefef;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​inet_server_port()</​tt>​ 
-| style="​background-color:#​efefef;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​int</​tt>​ 
-| style="​background-color:#​efefef;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| port of the local connection 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_my_temp_schema()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​oid</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| OID of session'​s temporary schema, or 0 if none 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_is_other_temp_schema(oid)</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​boolean</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| is schema another session'​s temporary schema? 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_postmaster_start_time()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​timestamp with time zone</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| server start time 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​pg_conf_load_time()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​timestamp with time zone</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| configuration load time 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​session_user</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| session user name 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​user</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​name</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| equivalent to&​nbsp;<​tt>​current_user</​tt>​ 
- 
-|- 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​version()</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​none;​padding:​0.0194in;"​| <​tt>​text</​tt>​ 
-| style="​background-color:#​ffffff;​border-top:​none;​border-bottom:​0.0361in double #​808080;​border-left:​0.0361in double #​808080;​border-right:​0.0361in double #​808080;​padding:​0.0194in;"​| PostgreSQL&​nbsp;​version information 
- 
-|} 
- 
- 
  
resources.txt ยท Last modified: 2020/08/10 02:35 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Run on Debian Driven by DokuWiki