Oracle DBA Forum

Oracle DBA Forum (http://odba.ru/index.php)
-   Инсталляция PostgreSQL (http://odba.ru/forumdisplay.php?f=56)
-   -   Команды psql (http://odba.ru/showthread.php?t=730)

Marley 11.03.2012 11:38

Команды psql
 
PSQL

# su - postgres
$ createdb mydatabase
$ psql mydatabase

Код:


psql (9.1.3)
Type "help" for help.

mydatabase=#

mydatabase-# \h

Код:


Available help:
  ABORT                            DECLARE
  ALTER AGGREGATE                  DELETE
  ALTER COLLATION                  DISCARD
  ALTER CONVERSION                DO
  ALTER DATABASE                  DROP AGGREGATE
  ALTER DEFAULT PRIVILEGES        DROP CAST
  ALTER DOMAIN                    DROP COLLATION
  ALTER EXTENSION                  DROP CONVERSION
  ALTER FOREIGN DATA WRAPPER      DROP DATABASE
  ALTER FOREIGN TABLE              DROP DOMAIN
  ALTER FUNCTION                  DROP EXTENSION
  ALTER GROUP                      DROP FOREIGN DATA WRAPPER
  ALTER INDEX                      DROP FOREIGN TABLE
  ALTER LANGUAGE                  DROP FUNCTION
  ALTER LARGE OBJECT              DROP GROUP
  ALTER OPERATOR                  DROP INDEX
  ALTER OPERATOR CLASS            DROP LANGUAGE
  ALTER OPERATOR FAMILY            DROP OPERATOR
  ALTER ROLE                      DROP OPERATOR CLASS
  ALTER SCHEMA                    DROP OPERATOR FAMILY
  ALTER SEQUENCE                  DROP OWNED
  ALTER SERVER                    DROP ROLE


mydatabase-# \h create database

Код:


Command:    CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
          [ TEMPLATE [=] template ]
          [ ENCODING [=] encoding ]
          [ LC_COLLATE [=] lc_collate ]
          [ LC_CTYPE [=] lc_ctype ]
          [ TABLESPACE [=] tablespace ]
          [ CONNECTION LIMIT [=] connlimit ] ]



mydatabase-# \?

Код:


General
  \copyright            show PostgreSQL usage and distribution terms
  \g [FILE] or ;        execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                    quit psql

Query Buffer
  \e [FILE] [LINE]      edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                    show the contents of the query buffer
  \r                    reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]        write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
mydatabase-#
mydatabase-# \?
General
  \copyright            show PostgreSQL usage and distribution terms
  \g [FILE] or ;        execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                    quit psql

Query Buffer
  \e [FILE] [LINE]      edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                    show the contents of the query buffer
  \r                    reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]        write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)


// Покажет представления и таблицы
mydatabase-# \d

// Покажет структуру таблицы, PK и FK
mydatabase-# \d table_name


// Покажет представления
mydatabase-# \dv


// Показать таблицы
mydatabase-# \dt



mydatabase-# \conninfo
You are connected to database "mydatabase" as user "postgres" via socket in "/tmp" at port "5432".


\pset format html
\pset format aligned

\pset border 0

// Отображать null как null
\pset null null

// Указать заголовок к таблице.
\pset title employee

Marley 11.03.2012 11:58

$ psql mydatabase

-- CREATE USER scott PASSWORD 'tiger' NOINHERIT LOGIN;
CREATE USER scott WITH PASSWORD 'tiger';

GRANT ALL PRIVILEGES ON DATABASE mydatabase to scott;

CREATE SCHEMA mySchema AUTHORIZATION scott;

quit

psql -U scott -d mydatabase

CREATE TABLE mySchema.records (id INT, txt TEXT);

INSERT INTO mySchema.records VALUES(1, 'This is mySchema');



==========================

// Показать имеющиеся базы данных
mydatabase=> SELECT datname FROM pg_database;

Код:


  datname
------------
 template1
 template0
 postgres
 mydatabase
(4 rows)


mydatabase=> \l

Код:

                                  List of databases
    Name    |  Owner  | Encoding |  Collate  |    Ctype    |  Access privile
ges
------------+----------+----------+-------------+-------------+-----------------
------
 mydatabase | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
    +
            |          |          |            |            | postgres=CTc/pos
tgres+
            |          |          |            |            | scott=CTc/postgr
es
 postgres  | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 |
 template0  | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
            |          |          |            |            | postgres=CTc/pos
tgres
 template1  | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
            |          |          |            |            | postgres=CTc/pos
tgres
(4 rows)


// Показать имеющиеся схемы и их владельцев.
mydatabase=> \dn

Код:


  List of schemas
  Name  |  Owner
----------+----------
 myschema | scott
 public  | postgres
(2 rows)



mydatabase=> select * from pg_user;

Код:


 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  |
 valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+
----------+-----------
 postgres |      10 | t          | t        | t        | t      | ******** |
          |
 scott    |    16385 | f          | f        | f        | f      | ******** |
          |
(2 rows)



mydatabase=# CREATE GROUP myGroup;
CREATE ROLE


mydatabase=# select * from pg_group;

Код:


 groname | grosysid | grolist
---------+----------+---------
 mygroup |    16393 | {}
(1 row)

mydatabase=# GRANT SELECT, INSERT ON TABLE mytable TO scott;

mydatabase=# ALTER GROUP myGroup ADD USER scott;




// SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE, and ALL PRIVILEGES
GRANT SELECT ON mySchema.records TO GROUP myGroup;





mydatabase=> \d myschema.*

Код:


  Table "myschema.records"
 Column |  Type  | Modifiers
--------+---------+-----------
 id    | integer |
 txt    | text    |


mydatabase=> SHOW search_path;
search_path
----------------
"$user",public
(1 row)



SET search_path TO myschema;

mydatabase=> \d

Код:


        List of relations
  Schema  |  Name  | Type  | Owner
----------+---------+-------+-------
 myschema | records | table | scott
(1 row)


To permanently alter the search path set on each connection, use

ALTER USER scott SET search_path TO myschema;

This change will only take effect after reconnecting to the database.


// Привилегии
mydatabase-# \dp

Код:


                              Access privileges
  Schema  |  Name  | Type  |  Access privileges  | Column access privileges
----------+---------+-------+---------------------+--------------------------
 myschema | records | table | scott=arwdDxt/scott+|
          |        |      | mygroup=r/scott    |
(1 row)


Marley 11.03.2012 16:45

Привилегии:

r - SELECT (Can read data from the object).
a - INSERT (Can insert data into the object).
w - UPDATE (Can change data in the object).
d - DELETE (Can delete data from the object).
R - RULE (Can create a rule on the table).
x - REFERENCES (Can create a foreign key to a table. Need this on both sides of the key).
t - TRIGGER (Can create a trigger on the table).
T - TEMPORARY (Can create a temporary table).
X - EXECUTE ( Can run the function).
U - USAGE (Can use the procedural language).
ALL - (All appropriate privileges. For tables, this equates to arwdRxt)

Marley 12.03.2012 10:32

phppgadmin
http://phppgadmin.sourceforge.net/doku.php?id=start



http://www.davidpashley.com/articles...istration.html
http://sql-info.de/postgresql/schemas.html


Текущее время: 09:48. Часовой пояс GMT +3.

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd. Перевод: zCarot