resources
Differences
This shows you the differences between two versions of the page.
|
resources [2011/11/28 21:39] 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. | ||
| - | **NOTE : Before you grant make sure you selected database else it could not work** | ||
| - | |||
| - | <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. | ||
| - | |||
| - | <code text> | ||
| - | Name Description | ||
| - | current_catalog name of current database (called "catalog" in the SQL standard) | ||
| - | current_database() name of current database | ||
| - | current_schema[()] name of current schema | ||
| - | current_schemas(boolean) names of schemas in search path, optionally including implicit schemas | ||
| - | current_user user name of current execution context | ||
| - | current_query() text of the currently executing query, as submitted by the client (might contain more than one statement) | ||
| - | pg_backend_pid() Process ID of the server process attached to the current session | ||
| - | pg_listening_channels() channel names that the session is currently listening on | ||
| - | inet_client_addr() address of the remote connection | ||
| - | inet_client_port() port of the remote connection | ||
| - | inet_server_addr() address of the local connection | ||
| - | inet_server_port() port of the local connection | ||
| - | pg_my_temp_schema() OID of session's temporary schema, or 0 if none | ||
| - | pg_is_other_temp_schema(oid) boolean is schema another session's temporary schema? | ||
| - | pg_postmaster_start_time() server start time | ||
| - | pg_conf_load_time() configuration load time | ||
| - | session_user session user name | ||
| - | user equivalent to current_user | ||
| - | version() PostgreSQL version information | ||
| - | </code> | ||
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
