postgresql

 

 


概念

 

schema

a schema is a higher level of organization,
a container or namespace within a database.

A schema contains a set of tables, views, stored procedures
and triggers and so on, all the usual components of a database.

Schema can be very powerful and useful way of partitioning data.

DB.Schema.Table

X select * from sales;
O select * from schema1.sales;
O select * from schema2.sales;

 


Type

 

character varying(n) Aliases "varchar"

variable-length with limit

character(n) Aliases "char"

fixed-length, blank padded

If the string to be stored is shorter than the declared length, values of type character will be space-padded

text

variable unlimited length

 


Sytnax

 

# create a schema
create schema petaluma;
create schema sausalito;

# CREATE TABLE
create table promotions(name varchar, value int);

Create DB

CREATE DATABASE jerry;

Create User

CREATE USER tom WITH PASSWORD 'myPassword';

Assign Permission

GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

List User

SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

 


Grid tool

 

Important:

In order to edit the data, each row in the table must be uniquely identifiable.

This can be done using the OID, or a primary key.

If none of them exist, the table is read only.

A column that’s part of the primary key will additionally be marked with [PK].

To enter a newline into a field, press Ctrl-Enter.

 


User Permission

 

 

pgAdmin > right-click on 'Group Roles' and select 'New Group Role'.

Under your server tree right-click 'Login Roles' > select 'New Login Role'

On the 'Role membership' tab add the newly create group to the 'Member' window > click 'OK'.

--

Expand 'Databases' > right-click on the database you would like to connect to Chartio > select 'Properties'.

In the 'Privileges' tab select the newly created group from the 'Role' drop-down menu > check the 'Connect' box > click 'Add/Change' > click 'OK'.

"CONNECT"

---

Click the expand arrow next to your database > expand 'Schemas' > right-click 'public' > select 'Grant Wizard'.

On the 'Selection' tab click 'Check All'.

In the 'Privileges' tab select the newly created group from the 'Role' drop-down menu > check the 'Select' box > click 'Add/Change' > click 'OK'.

---

 


Set Primary & Foreign Keys in pgAdmin

 

Step

    Select the table you want
    Ctrl+Alt+Enter or right-click / Properties
    Select "Constraints" tab
    At the left-bottom side of the form you will see the option "Primary Key"
    Click add
    Select "Columns" tab
    Select the column you want as a key
    Click add

 


Table

 

Modify table COLUME

"You are not logged on as the database owner or ..."

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

 


auto increment

 

SERIAL is the equivalent function.
 


pg_dump

 

If the backup is successful, the Messages tab will display:

    Process returned exit code 0.
    
pg_dump: aborting because of server version mismatch

        Select Plain to generate a plain-text script file containing SQL statements and commands that you can execute at the psql command line or with pg_dump to recreate the database objects and load the table data. A plain-text backup file can easily be edited in a text editor if desired before restoring the database objects with the psql program. Plain-text format is normally recommended for smaller databases.
        
        Select Custom to generate a pg_dump formatted binary file that allows for restoration of all or only selected database objects from the backup file. You can use pgAdmin to restore from a custom archive backup file. A custom archive backup file cannot be edited, but you can use pgAdmin to select which database objects to restore from the backup file. Custom archive format is recommended for medium to large databases from which you may want to select the database objects to restore from the backup file.
        
        Select Tar to generate a tar archive file that allows for restoration of all or only selected database objects from the backup file. You can use pgAdmin to restore from a tar archive backup file.
        
        Select Directory to generate a directory-format archive suitable for use with pgAdmin’s Restore dialog or pg_restore. This file format creates a directory with one file for each table and blob being dumped, plus a Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default and supports parallel dumps.

        
pg_dump books > books.out

pg_dump: server version: 9.6.3; pg_dump version: 9.5.5
pg_dump: aborting because of server version mismatch

Every time you upgrade or re install a new version of PostgreSQL,
a latest version of  pg_dump is installed.
There must be a  PostgreSQL/bin directory somewhere on your system,

----------

Compress Ratio

field to select a compression level for the backup. Specify a value of zero to mean use no compression; specify a maximum compression value of 9.

----------

 

 


Data Folder

 

<POSTGRESQL_DIRECTORY>/data/base/<OID>

# You can create tablespaces to store database objects in other parts of the filesystem.

SELECT * FROM pg_tablespace;

#

SELECT oid from pg_database where datname = '<dbname>'

#

SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>;