Skip to content

Managing Tables in Postgresql

Create Operations

Creating a table named "collection"

create table collection(
id serial primary key,
name varchar(50) Unique not null,
description text not null,
created_on timestamp not null);

Describing the table named "collection"

\d collection

                                         Table "public.collection"
   Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+----------------------------------------
 id          | integer                     |           | not null | nextval('collection_id_seq'::regclass)
 name        | character varying(50)       |           | not null |
 description | text                        |           | not null |
 created_on  | timestamp without time zone |           | not null |
Indexes:
    "collection_pkey" PRIMARY KEY, btree (id)
    "collection_name_key" UNIQUE CONSTRAINT, btree (name)

Creating post table

Create table post(
id serial primary key,
collection_id int not null,
title char(100) not null,
body text not null,
is_public boolean,
created_on timestamp not null,
Foreign Key (collection_id) References collection(id));

                                          Table "public.post"
    Column     |            Type             | Collation | Nullable |             Default
---------------+-----------------------------+-----------+----------+----------------------------------
 id            | integer                     |           | not null | nextval('post_id_seq'::regclass)
 collection_id | integer                     |           | not null |
 title         | character(100)              |           | not null |
 body          | text                        |           | not null |
 is_public     | boolean                     |           |          |
 created_on    | timestamp without time zone |           | not null |
Indexes:
    "post_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "post_collection_id_fkey" FOREIGN KEY (collection_id) REFERENCES collection(id)

Alter Operation

Rename the table

Alter table post rename to posts;

Drop a column

Alter table post drop column is_public;

Add a new column

Alter table post Add column is_public boolean not null;

Rename the column

Alter table post rename column is_public to is_private;

Drop Primary Key Constraint

alter table post drop constraint post_pkey;

Add Primary Key Constraint

Alter table post Add Primary key (id);

Modify existing column structure

  • Set the default value to false Alter table post alter column is_private set default false;
  • Drop the not null constraint Alter table post Alter column is_private drop not null;
  • Add unique constraint Alter table post Add constraint title_unique UNIQUE (title);
  • Change data type of column Alter table post Alter column title Type varchar(100);

Rename Table

alter table post rename to posts;

Drop Table

  • Syntax:

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

  • Example:

DROP TABLE comments;

Truncate Table

  • The TRUNCATE TABLE statement deletes all data from a table without scanning it.

  • This is the reason why it is faster than the DELETE statement.

  • In addition, the TRUNCATE TABLE statement reclaims the storage right away so you do not have to perform a subsequent VACUMM operation, which is useful in the case of large tables.

  • Syntax:

TRUNCATE TABLE table_name;

  • Example:

Truncate table posts;

  • By default, the TRUNCATE TABLE statement does not remove any data from the table that has foreign key references.

  • To remove data from a table and other tables that have foreign key reference the table, you use CASCADE option in the TRUNCATE TABLE statement as follows :

TRUNCATE TABLE posts CASCADE;

  • The TRUNCATE TABLE does not fire ON DELETE trigger. Instead, it fires the BEFORE TRUNCATE and AFTER TRUNCATE triggers.

  • The TRUNCATE TABLE statement is transaction-safe.

Temporary Table

  • A temporary table, as its named implied, is a short-lived table that exists for the duration of a database session.

  • PostgreSQL automatically drops the temporary tables at the end of a session or a transaction.

  • Creating a temporary table:

CREATE TEMPORARY TABLE temp_table_name( column_list );

  • Dropping a temporary table

DROP TABLE temp_table_name;

Copy the table

  • Without data

CREATE TABLE blog AS TABLE posts WITH NO DATA;

  • With data CREATE TABLE blog AS TABLE posts;
  • With partial data CREATE TABLE blog AS SELECT * FROM posts WHERE id>100;