Managing PostgreSQL with plsql Commands

psql Commands

 

 

To see the database version

 

C:\Program Files\PostgreSQL\9.5\bin>psql –version

psql (PostgreSQL) 9.5.2

 

C:\Program Files\PostgreSQL\9.5\bin>

 

List Databases and then quit

 

C:\Program Files\PostgreSQL\9.5\bin>psql -l -U postgres

 

List of databases

Name    |  Owner   | Encoding |          Collate           |           Ctype            |   Access privileges

———–+———-+———-+—————————-+—————————-+———————–

postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +

|          |          |                            |                            | postgres=CTc/postgres

template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +

|          |          |                            |                            | postgres=CTc/postgres

(3 rows)

 

C:\Program Files\PostgreSQL\9.5\bin>

 

Getting Help for psql

 

C:\Program Files\PostgreSQL\9.5\bin>psql –help

psql is the PostgreSQL interactive terminal.

 

Usage:

psql [OPTION]… [DBNAME [USERNAME]]

 

General options:

-c, –command=COMMAND    run only single command (SQL or internal) and exit

-d, –dbname=DBNAME      database name to connect to (default: “Administrator”)

-f, –file=FILENAME      execute commands from file, then exit

-l, –list               list available databases, then exit

-v, –set=, –variable=NAME=VALUE

set psql variable NAME to VALUE

(e.g., -v ON_ERROR_STOP=1)

-V, –version            output version information, then exit

-X, –no-psqlrc          do not read startup file (~/.psqlrc)

-1 (“one”), –single-transaction

execute as a single transaction (if non-interactive)

-?, –help[=options]     show this help, then exit

–help=commands      list backslash commands, then exit

–help=variables     list special variables, then exit

 

Input and output options:

-a, –echo-all           echo all input from script

-b, –echo-errors        echo failed commands

-e, –echo-queries       echo commands sent to server

-E, –echo-hidden        display queries that internal commands generate

-L, –log-file=FILENAME  send session log to file

-n, –no-readline        disable enhanced command line editing (readline)

-o, –output=FILENAME    send query results to file (or |pipe)

-q, –quiet              run quietly (no messages, only query output)

-s, –single-step        single-step mode (confirm each query)

-S, –single-line        single-line mode (end of line terminates SQL command)

 

Output format options:

-A, –no-align           unaligned table output mode

-F, –field-separator=STRING

field separator for unaligned output (default: “|”)

-H, –html               HTML table output mode

-P, –pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)

-R, –record-separator=STRING

record separator for unaligned output (default: newline)

-t, –tuples-only        print rows only

-T, –table-attr=TEXT    set HTML table tag attributes (e.g., width, border)

-x, –expanded           turn on expanded table output

-z, –field-separator-zero

set field separator for unaligned output to zero byte

-0, –record-separator-zero

set record separator for unaligned output to zero byte

 

Connection options:

-h, –host=HOSTNAME      database server host or socket directory (default: “local socket”)

-p, –port=PORT          database server port (default: “5432”)

-U, –username=USERNAME  database user name (default: “Administrator”)

-w, –no-password        never prompt for password

-W, –password           force password prompt (should happen automatically)

 

For more information, type “\?” (for internal commands) or “\help” (for SQL

commands) from within psql, or consult the psql section in the PostgreSQL

documentation.

 

Report bugs to <pgsql-bugs@postgresql.org>.

 

C:\Program Files\PostgreSQL\9.5\bin>

 

 

 

 

PGUSER Linux environment Variables

 

Holds user name to connect  to the database

Normally unset it

 

unset PGUSER

Defaults DBs

  • postgres : master DB to control RDBMS in Cluster databases.
  • template0 — used to reconstruct template1 ,
  • template1 – – copy of template0 is used to generate new DB s

 

Connect to Database interactively with postgres user

 

C:\Program Files\PostgreSQL\9.5\bin>psql –U postgres

psql (9.5.2)

WARNING: Console code page (437) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page “Notes for Windows users” for details.

Type “help” for help.

 

postgres=#  à This shows It is a super user.

 

SQL Specific Help

 

postgres=#  \h

Available help:

ABORT                            DEALLOCATE

ALTER AGGREGATE                  DECLARE

ALTER COLLATION                  DELETE

ALTER CONVERSION                 DISCARD

ALTER DATABASE                   DO

ALTER DEFAULT PRIVILEGES         DROP AGGREGATE

ALTER DOMAIN                     DROP CAST

ALTER EVENT TRIGGER              DROP COLLATION

ALTER EXTENSION                  DROP CONVERSION

ALTER FOREIGN DATA WRAPPER       DROP DATABASE

ALTER FOREIGN TABLE              DROP DOMAIN

ALTER FUNCTION                   DROP EVENT TRIGGER

ALTER GROUP                      DROP EXTENSION

ALTER INDEX                      DROP FOREIGN DATA WRAPPER

ALTER LANGUAGE                   DROP FOREIGN TABLE

ALTER LARGE OBJECT               DROP FUNCTION

ALTER MATERIALIZED VIEW          DROP GROUP

ALTER OPERATOR                   DROP INDEX

ALTER OPERATOR CLASS             DROP LANGUAGE

ALTER OPERATOR FAMILY            DROP MATERIALIZED VIEW

ALTER POLICY                     DROP OPERATOR

ALTER ROLE                       DROP OPERATOR CLASS

ALTER RULE                       DROP OPERATOR FAMILY

ALTER SCHEMA                     DROP OWNED

ALTER SEQUENCE                   DROP POLICY

ALTER SERVER                     DROP ROLE

ALTER SYSTEM                     DROP RULE

ALTER TABLE                      DROP SCHEMA

ALTER TABLESPACE                 DROP SEQUENCE

ALTER TEXT SEARCH CONFIGURATION  DROP SERVER

ALTER TEXT SEARCH DICTIONARY     DROP TABLE

ALTER TEXT SEARCH PARSER         DROP TABLESPACE

ALTER TEXT SEARCH TEMPLATE       DROP TEXT SEARCH CONFIGURATION

ALTER TRIGGER                    DROP TEXT SEARCH DICTIONARY

ALTER TYPE                       DROP TEXT SEARCH PARSER

ALTER USER                       DROP TEXT SEARCH TEMPLATE

ALTER USER MAPPING               DROP TRANSFORM

ALTER VIEW                       DROP TRIGGER

ANALYZE                          DROP TYPE

BEGIN                            DROP USER

CHECKPOINT                       DROP USER MAPPING

CLOSE                            DROP VIEW

CLUSTER                          END

COMMENT                          EXECUTE

COMMIT                           EXPLAIN

COMMIT PREPARED                  FETCH

COPY                             GRANT

CREATE AGGREGATE                 IMPORT FOREIGN SCHEMA

— More  —

 

postgres=# \h CREATE TABLE

 

Command:     CREATE TABLE

Description: define a new table

Syntax:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_

{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]

| table_constraint

| LIKE source_table [ like_option … ] }

[, … ]

] )

[ INHERITS ( parent_table [, … ] ) ]

[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

 

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_

OF type_name [ (

{ column_name WITH OPTIONS [ column_constraint [ … ] ]

| table_constraint }

[, … ]

) ]

[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

 

where column_constraint is:

 

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

CHECK ( expression ) [ NO INHERIT ] |

DEFAULT default_expr |

UNIQUE index_parameters |

PRIMARY KEY index_parameters |

REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE action ] [ ON UPDATE action ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

 

 

Psql  Specific Help

 

postgres-# \?

General

\copyright             show PostgreSQL usage and distribution terms

\g [FILE] or ;         execute query (and send results to file or |pipe)

\gset [PREFIX]         execute query and store results in psql variables

\q                     quit psql

\watch [SEC]           execute query every SEC seconds

 

Help

\? [commands]          show help on backslash commands

\? options             show help on psql command-line options

\? variables           show help on special variables

\h [NAME]              help on syntax of SQL commands, * for all commands

 

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

\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

\ir FILE               as \i, but relative to location of current script

\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)

\d[S+]                 list tables, views, and sequences

\d[S+]  NAME           describe table, view, sequence, or index

\da[S]  [PATTERN]      list aggregates

\db[+]  [PATTERN]      list tablespaces

\dc[S+] [PATTERN]      list conversions

\dC[+]  [PATTERN]      list casts

\dd[S]  [PATTERN]      show object descriptions not displayed elsewhere

\ddp    [PATTERN]      list default privileges

\dD[S+] [PATTERN]      list domains

 

Some Special Plsql Commands

 

 

postgres-# \l+   Show Detailed information about databases

 

postgres-# \du+   Return detailed list of users

List of roles

Role name |                         Attributes                         | Member of | Description

———–+————————————————————+———–+————-

postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

 

postgres-# \! Returns to shell

 

postgres-# \q to quit

 

 

postgres=# select version();

version

————————————————————-

PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 64-bit

(1 row)

 

 

psql –U postgres –f filename.sql  to run a sql file

 

template1=# \c postgres  to connect to anaother database

WARNING: Console code page (437) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page “Notes for Windows users” for details.

You are now connected to database “postgres” as user “postgres”.

postgres=#

 

\c DBNAME RemoteHOSTNAME   connect to Remote Host and database

to connect to a database

 

C:\Program Files\PostgreSQL\9.5\bin>psql -U postgres template1

psql (9.5.2)

WARNING: Console code page (437) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page “Notes for Windows users” for details.

Type “help” for help.

 

template1=#

 

 

List Database Objects

 

postgres=# \dS

 

 

 

postgres=# \dS+

List of relations

Schema   |              Name               | Type  |  Owner   |    Size    | Description

————+———————————+——-+———-+————+————-

pg_catalog | pg_aggregate                    | table | postgres | 48 kB      |

pg_catalog | pg_am                           | table | postgres | 40 kB      |

pg_catalog | pg_amop                         | table | postgres | 80 kB      |

pg_catalog | pg_amproc                       | table | postgres | 64 kB      |

pg_catalog | pg_attrdef                      | table | postgres | 8192 bytes |

pg_catalog | pg_attribute                    | table | postgres | 384 kB     |

pg_catalog | pg_auth_members                 | table | postgres | 0 bytes    |

pg_catalog | pg_authid                       | table | postgres | 40 kB      |

pg_catalog | pg_available_extension_versions | view  | postgres | 0 bytes    |

pg_catalog | pg_available_extensions         | view  | postgres | 0 bytes    |

pg_catalog | pg_cast                         | table | postgres | 48 kB      |

pg_catalog | pg_class                        | table | postgres | 136 kB     |

pg_catalog | pg_collation                    | table | postgres | 40 kB      |

pg_catalog | pg_constraint                   | table | postgres | 48 kB      |

pg_catalog | pg_conversion                   | table | postgres | 56 kB      |

pg_catalog | pg_cursors                      | view  | postgres | 0 bytes    |

pg_catalog | pg_database                     | table | postgres | 8192 bytes |

pg_catalog | pg_db_role_setting              | table | postgres | 8192 bytes |

pg_catalog | pg_default_acl                  | table | postgres | 0 bytes    |

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s