postgresql 中表的相关操作

2) The SQL Language

2-1) Managing databases

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Parameters:

• name
  The name of a database to create.

• user_name
  The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.

• template
  The name of the template from which to create the new database, or DEFAULT to use the default template (template1).

• encoding
  Character set encoding to use in the new database. Specify a string constant (e.g., ‘SQL_ASCII’), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). The character sets supported by the PostgreSQL server are described in Section 22.2.1. See below for additional restrictions.

• lc_collate
  Collation order (LC_COLLATE) to use in the new database. This affects the sort order applied to strings, e.g. in queries with ORDER BY, as well as the order used in indexes on text columns. The default is to use the collation order of the template database. See below for additional restrictions.

• lc_ctype
  Character classification (LC_CTYPE) to use in the new database. This affects the categorization of characters, e.g. lower, upper and digit. The default is to use the character classification of the template database. See below for additional restrictions.

• tablespace
  The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information.

• connlimit
  How many concurrent connections can be made to this database. -1 (the default) means no limit.

2-2) Managing tables

2-2-1) Create table syntax

create table table_name (
    column_name type column_constraint,
    table_constraint
)inherits existing_table_name;

Postgresql column constraints:
* not null
* unique
* primary key
* check
* references
* Exclusion Constraints

PostgreSQL table constaints:
* unique (column_list)
* primary key(column_list)
* check (condition)
* references

Create table example

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

CREATE TABLE employees (
 id serial PRIMARY KEY,
 first_name VARCHAR (50),
 last_name VARCHAR (50),
 birth_date DATE CHECK (birth_date > '1900-01-01'),
 joined_date DATE CHECK (joined_date > birth_date),
 salary numeric CHECK(salary > 0)
);

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

2-2-2) Alter table syntax

The syntax of the alter table is as following:

ALTER TABLE table_name action;

PostgreSQL provides many actions that allow you to:
* Add a column, drop a column, rename a column, or change a columns data type.
* Set a default value for the column.
* Add a CHECK constraint to a column.
* Rename a table.

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT]

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL]

ALTER TABLE table_name ADD CHECK expression;

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition

ALTER TABLE table_name RENAME TO new_table_name;

2-2-3) Creating a temporary table

A temporary table, as its name implied, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables ta the end of a session or a transaction.

CREATE TEMPORARY TABLE temp_table(
   ...
);

CREATE TEMP TABLE temp_table(
   ...
);

Note that a temporary table is visible only to the session that creates it. In order words, it remains invisible to other sessions

2-2-4) Truncate table

Removing all data from one table

TRUNCATE TABLE table_name;

PostgreSQL truncate table not only allows you remove all data from a table but also reset associated sequence generator by specifying reset identity option as follows:

TRUNCATE TABLE table_name RESET IDENTITY;

Removing all data from multiple tables To remove all data from multiple tables at once, you separate each table by a comma ‘,’ as follows:

TRUNCATE TABLE table_name1, table_name2,...

Removing all data from table that has foreign key references
To remove data from the main table and other tables that have foreign key references to the main table, you can use the cascade option as follows:

TRUNCATE TABLE table_name CASCADE;

Note that you should be careful when you use the cascade option, or else you might potentially delete data from the tables that you did not intend to.

TRUNCATE TABLE and ON DELETE trigger
Even though the TRUNCATE TABLE statement removes all data from a table, it does not fire any on delete trigger associated with that table

To fire the trigger when the TRUNCATE TABLE command applied to a table, you must define BEFORE TRUNCATE and/or AFTER TRUNCATE triggers for that table.