Postgresql User Notes - Administration Basics

Notes on administration of PostgreSql database.

Change a column type

ALTER TABLE table_name ALTER COLUMN  column_name TYPE column_type USING (column_name::column_type);

Example - Change column type text to integer

ALTER TABLE customer_address ALTER COLUMN updated_by  TYPE integer USING (updated_by::integer);

Retrieve and update auto id

SELECT MAX(id) from product ;

SELECT nextval('product_id_seq');

SELECT setval('product_id_seq', (SELECT MAX(id) FROM product )+1);

Delete a column or more

ALTER TABLE product DROP product_type; 

ALTER TABLE product DROP product_type, DROP product_rating; 

Templates

Assign a database to be used as template to create other databases.

ALTER DATABASE database_name WITH is_template true;

Example

ALTER DATABASE database_name WITH is_template true;