The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database.
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
GLOBAL TEMPORARY and GLOBAL TEMP
Optional. If either of these are specified, he table is a global temporary table.
LOCAL TEMPORARY and LOCAL TEMP
Optional. If either of these are specified, the table is a local temporary table.
UNLOGGED
Optional. If specified, the data in the table is not written to the write-ahead log. This improves performance on the table, however, the data in this table will be lost if a crash occurs.
IF NOT EXISTS
Optional. If specified, the CREATE TABLE statement will not raise an error if the tables already exists.
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
datatype
The data type for the column.
CONSTRAINT constraint_name
Optional. The name of the constraint.
NULL or NOT NULL
Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.
DEFAULT default_value
Optional. It is the value to assign to the column if left blank or NULL.
PRACTICALS FOR CREATE TABLE 1:
Step 1.Following is an example, which creates a "employe" table without constraints
Command Line views:
Step 1.Following is an another example, which creates a "student" table with constraints of primary key
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
- CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command.
- If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table. The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema.
- CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.
- The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways.
CREATE [ [ GLOBAL TEMPORARY
| GLOBAL TEMP
| LOCAL TEMPORARY
| LOCAL TEMP
| UNLOGGED ]
TABLE [IF NOT EXISTS] table_name
(
column1 datatype [ COLLATE collation ]
[ CONSTRAINT constraint_name ]
{ NULL
| NOT NULL
| CHECK ( expression ) [ NO INHERIT ]
| DEFAULT default_value
| UNIQUE index_parameters
| PRIMARY KEY index_parameters
| REFERENCES ref_table [ ( ref_column ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ]
[ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ],
column2 datatype [ COLLATE collation ]
[ CONSTRAINT constraint_name ]
{ NULL
| NOT NULL
| CHECK ( expression ) [ NO INHERIT ]
| DEFAULT default_value
| UNIQUE index_parameters
| PRIMARY KEY index_parameters
| REFERENCES ref_table [ ( ref_column ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ]
[ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ],
...
| [ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ]
| UNIQUE ( index_col_name, ... )
| PRIMARY KEY ( index_col_name, ... )
| FOREIGN KEY ( index_col_name, ... )
REFERENCES another_table_name (index_col_name, ...)
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ]
[ ON UPDATE action ]
| LIKE source_table
{ INCLUDING | EXCLUDING }
{ DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
);
Parameters:GLOBAL TEMPORARY and GLOBAL TEMP
Optional. If either of these are specified, he table is a global temporary table.
LOCAL TEMPORARY and LOCAL TEMP
Optional. If either of these are specified, the table is a local temporary table.
UNLOGGED
Optional. If specified, the data in the table is not written to the write-ahead log. This improves performance on the table, however, the data in this table will be lost if a crash occurs.
IF NOT EXISTS
Optional. If specified, the CREATE TABLE statement will not raise an error if the tables already exists.
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
datatype
The data type for the column.
CONSTRAINT constraint_name
Optional. The name of the constraint.
NULL or NOT NULL
Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.
DEFAULT default_value
Optional. It is the value to assign to the column if left blank or NULL.
PRACTICALS FOR CREATE TABLE 1:
Step 1.Following is an example, which creates a "employe" table without constraints
postgres=#CREATE TABLE employe (
name text,
age integer,
designation text,
salary integer
);
CREATE TABLE
Step 2.You can verify the table using Following "Collected statistics views" & "Command Line views" if your table has been created successful or notCommand Line views:
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\dp [PATTERN] list table, view, and sequence access privileges
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
Collected statistics views:- pg_stat_all_tables One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_all_tables for details.
- pg_stat_sys_tables Same as pg_stat_all_tables, except that only system tables are shown.
- pg_stat_user_tables Same as pg_stat_all_tables, except that only user tables are shown.
- pg_stat_xact_all_tables Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not present in this view.
- pg_stat_xact_sys_tables Same as pg_stat_xact_all_tables, except that only system tables are shown.
- pg_stat_xact_user_tables Same as pg_stat_xact_all_tables, except that only user tables are shown.
- pg_stat_all_indexes One row for each index in the current database, showing statistics about accesses to that specific index. See pg_stat_all_indexes for details.
- pg_stat_sys_indexes Same as pg_stat_all_indexes, except that only indexes on system tables are shown.
- pg_stat_user_indexes Same as pg_stat_all_indexes, except that only indexes on user tables are shown.
- pg_statio_all_tables One row for each table in the current database, showing statistics about I/O on that specific table. See pg_statio_all_tables for details.
- pg_statio_sys_tables Same as pg_statio_all_tables, except that only system tables are shown.
- pg_statio_user_tables Same as pg_statio_all_tables, except that only user tables are shown.
postgres=# insert into employe values('nijam',20,'tutorial',100000);
INSERT 0 1
Step 4.use "/d" which will be used to list down all the tables in an attached databasepostgres=# \d
Above command will produce the following result
Schema | Name | Type | Owner
--------+------------------------------+----------+----------
public | details | table | postgres
public | employe | table | postgres
public | place | table | postgres
public | t5 | table | u2
public | us_address | table | postgres
public | us_address_address_id_seq | sequence | postgres
public | us_snail_addy | table | postgres
public | us_snail_addy_address_id_seq | sequence | postgres
Step 5.Now we can see "employe" table meta data using "\d employe" this command will shows table information of table constraint & data types postgres=# \d employe
Above command will produce the following resultTable "public.employe" Column | Type | Modifiers -------------+---------+----------- name | text | age | integer | designation | text | salary | integer |another example is
postgres=# \dp employe
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
public | employe | table | |
(1 row)
Step 6.Describe the "pg_stat_all_tables" views using '\d' View "pg_catalog.pg_stat_all_tables"
Column | Type | Modifiers
-------------------+--------------------------+-----------
relid | oid |
schemaname | name |
relname | name |
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
n_tup_hot_upd | bigint |
n_live_tup | bigint |
n_dead_tup | bigint |
last_vacuum | timestamp with time zone |
last_autovacuum | timestamp with time zone |
last_analyze | timestamp with time zone |
last_autoanalyze | timestamp with time zone |
vacuum_count | bigint |
autovacuum_count | bigint |
analyze_count | bigint |
autoanalyze_count | bigint |
postgres=# select relid,schemaname,relname,seq_scan,n_dead_tup,vacuum_count,analyze_count from pg_stat_all_tables where relname='employe';
relid | schemaname | relname | seq_scan | n_dead_tup | vacuum_count | analyze_count
-------+------------+---------+----------+------------+--------------+---------------
24648 | public | employe | 1 | 0 | 0 | 0
(1 row)
PRACTICAL FOR TABLE CREATION 2:Step 1.Following is an another example, which creates a "student" table with constraints of primary key
postgres=#CREATE TABLE student(
id integer CONSTRAINT pk PRIMARY KEY,
name text,
age integer,
designation text,
salary integer
);
CREATE TABLE
Step 2.Describe the student table here you can see "pk" constraint that's called primary keypostgres=# \d student
Table "public.student"
Column | Type | Modifiers
-------------+---------+-----------
id | integer | not null
name | text |
age | integer |
designation | text |
salary | integer |
Indexes:
"pk" PRIMARY KEY, btree (id)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------+----------+----------
public | details | table | postgres
public | employe | table | postgres
public | place | table | postgres
public | student | table | postgres
public | t5 | table | u2
public | us_address | table | postgres
public | us_address_address_id_seq | sequence | postgres
public | us_snail_addy | table | postgres
public | us_snail_addy_address_id_seq | sequence | postgres
No comments:
Post a Comment