| At line 1 changed one line |
| !psql |
| !psql - command line database query tool |
| At line 7 changed one line |
| to see all output: |
| to save all output: |
| At line 17 added 2 lines |
| \dn list schemas |
| set search path to <schema>; |
| At line 20 added 2 lines |
| \d table display schema for table |
| \pset pager off |
| At line 27 added 7 lines |
| show all; |
| show server_version; |
| show config_file; |
| show data_directory; |
| show hba_file; |
| }}} |
| {{{ |
| At line 38 added one line |
| ! Data Types |
| At line 40 added 23 lines |
| | smallint | 2 |
| | integer | 4 |
| | bigint | 8 |
| | real | 4 |
| | double precision | 8 |
| | smallserial | 2 |
| | serial | 4 |
| | bigserial | 8 |
| | money | 8 |
| | char(n) | |
| | varchar(n) | |
| | text | variable length, unlimited |
| | bytea | binary, variable length |
| | timestamp | date and time | not null default current_timestamp |
| | date | |
|
| --------------------------------------------------------------------------- |
|
| ! Passing a password into psql |
|
| {{{export PGPASSWORD=ThePassword}}} |
| --------------------------------------------------------------------------- |
|
| At line 31 changed one line |
| psql arahant postgres -c 'select screen_id, substring(filename from 24) as "File Name", name from screen order by filename;' >output.txt |
| psql mydb postgres -c 'select screen_id, substring(filename from 24) as "File Name", name from screen order by filename;' >output.txt |
|
| psql mydb postgres -f file-name >output.txt |
| At line 118 added 37 lines |
| !Clone a database |
|
| CREATE DATABASE <newdb> WITH TEMPLATE = <olddb>; |
|
| --------------------------------------------------------------------------- |
|
| !Schemas |
|
| create schema <schema-name>; |
|
| drop schema <schema-name> cascade; |
|
| --------------------------------------------------------------------------- |
|
| !CSV file format |
|
| Exporting: |
| {{{ |
| copy (select lname, fname, personal_email from person where personal_email <> '') |
| to '/tmp/file.csv' |
| with (format csv); |
| }}} |
|
| Importing: |
| {{{ |
| copy person (user_id, user_lname, user_fname, email_address, user_password, birth_date, sex, when_added) |
| from '/tmp/sample-users.csv' |
| with (format csv); |
| }}} |
|
| The csv files must be in the /tmp directory or somewhere that is globally readable because this command is run by a process that is connected to the postgres user. It, therefore, can only access files that it has permission to read. |
|
| The import command may exclude the column list if all the columns are used and all of the columns are in the order specified in the schema. |
|
|
| --------------------------------------------------------------------------- |
|
| At line 177 removed one line |
|