create_database
- create database database_name
- with owner=x
- with template=x. template0 is the basis for template1.
the template0 is used when recreating a pg_dump'ed database, because this template is the freshest out there. Otherwise, the default is template1
- with encoding=x (SQL_ASCII,ECMA-121,ISO-8859-8)
- create database korean with encoding = 'EUC_KR';
- with encoding = 'EUC_KR'
- with location=x
- CREATE DATABASE name WITH LOCATION = 'location'
(source: postgresql by Korry and Susan Doublas, Developer'slibrary)
export PG_ALTERNATE=/bigdrive/pgdata
initlocation PG_ALTERNATE (note: not a bash $)
pg_ctl restart -D $PGDATA
psql -q -d database_name
create database new_db with location=PG_ALTERNATE
also can use the createdb -O username dbname command here.
create_table
here for creating key which uses your specified sequence
- create TEMPORARY TABLE ...
- create TABLE
- ---> creates a table in the database you're in
- CREATE TABLE table_name (column_name1 type [modifiers] [, column_name2 type [modifiers]])
-
- create table tapes ( tape_id CHARACTER(8) UNIQUE, title CHARACTER VARYING(80), distributor CHARACTER VARYING(80)); (source: PostGresql by the douglasses).
- create table customers (customer_id INTEGER UNIQUE, customer_name VARCHAR(50), phone CHAR(8), birth_date DATE, balane NUMERIC(7,2)); (same source as above)
- create table ... balance DECIMAL(7,2)...... id_number INTEGER UNIQUE NOT NULL .....
- create table account_status ( .... balance DECIMAL(7,2) CONSTRAINT invalid_order CHECK ( balance > 0 AND balance < 10000));
create table with sequence
- create table people (person_id serial, lname varchar, fname varchar, job varchar);
NOTICE: CREATE TABLE will create implicit sequence 'people_person_id_seq' for SERIAL column 'people.person_id'
hence, serial creates a kind of sequence.
- create table employees
(id serial NOT NULL PRIMARY KEY),
lname varchar NOT NULL,
fname varchar);
create table with references
- REFERENCES
create table sales (
sale_id serial NOT NULL PRIMARY KEY,
customer int8
REFERENCES customers(customer_id)
on update cascade
on delete restrict,
amount money,
date date
);
- CREATE TABLE childtable (...) INHERITS (parenttable);
create_other
- view
- creat view as ....
- schema
- create schema joe-schmoe
- set search_path to 'joe-schmoe','joe-schmoe-old';
- index
- create index indexindex on indexes using btree (symid);
- create index symbolindex on symbols using btree (symname);
- create index usageindex on usage using btree (symid);
- create index filelookup on files using btree (filename);
- sequence
- create sequence mysequence start 10;
- create sequence filenum cache 50;
- create sequence symnum cache 50;
- rule:
constraints
CONSTRAINT $primary_key_name PRIMARY KEY ($primary_key)
column_types
data_types.htm
return to top
