Create database user, alter and drop username

PostreSQL tutorial – Create/Alter/Drop User Create postgresql user Create database user, alter user password and drop username. Rename, modify user. Syntax CREATE USER user_name; Example CREATE USER user_test; Alter postgresql user Syntax ALTER USER user_name [ [ WITH ] option [ … ] ] where option can be: SUPERUSER | NOSUPERUSER CREATEDB | NOCREATEDB CREATEROLE | NOCREATEROLE CREATEUSER… Read More »

Create table type, alter and drop type

PostreSQL tutorial – Create/Alter/Drop Type Create postgresql type Create table type, alter and drop type. Syntax and examples. Syntax CREATE TYPE type_name AS ( attribute_name_1 data_type, attribute_name_2 data_type, … attribute_name_n data_type ); CREATE TYPE type_name AS ENUM ( [ ‘label’ [, … ] ] ); Example CREATE TYPE customer_type AS (t_id numeric, t_name varchar); CREATE TYPE log_status AS… Read More »

Create table, Create sequence

PostreSQL tutorial – Create/Alter/Drop Table Create postgresql sequence Create table syntax and example, Create sequence syntax. Syntax CREATE SEQUENCE sequence_name INCREMENT 1 MINVALUE 1 MAXVALUE 9999999999999999 START 1 CACHE 1; Example CREATE SEQUENCE seq_customers INCREMENT 1 MINVALUE 1 MAXVALUE 9999999999999999 START 1 CACHE 1; Create postgresql table Syntax CREATE TABLE [IF NOT EXISTS] table_name( column_name_1 data_type, column_name_2 data_type,… Read More »

Create schema syntax, Alter schema

PostreSQL tutorial – Create/Alter/Drop Schema Create postgresql schema Create schema syntax, Alter schema with examples. Syntax CREATE SCHEMA schema_name [ schema_element [ … ] ]; CREATE SCHEMA AUTHORIZATION username [ schema_element [ … ] ]; Example CREATE SCHEMA test; Create a schema for user user_1, and schema will be created automatically with the name _user_1: CREATE SCHEMA AUTHORIZATION… Read More »

Create database, Alter database examples

PostreSQL tutorial – Create/Alter/Drop Database Create postgresql database Create database syntax, alter database examples, rename and drop. Syntax CREATE DATABASE db_name [ [ WITH ] [ OWNER [=] username ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT… Read More »

Control Structures syntax: if then else case

PostreSQL tutorial – Control Structures PostgreSQL Conditionals Control Structures syntax: if then else, case when, return, exit, loop. Syntax IF expression THEN statement END IF; IF expression THEN statement_1 ELSE statement_2 END IF; IF expression_1 THEN statement_1 ELSIF expression_2 THEN statement_2 ELSE statement_3 END IF; CASE expression_1 WHEN expression_2 THEN statement_1 ELSE statement_2 END CASE; CASE WHEN expression… Read More »

Case – When – Then

Case Syntax and examples of conditional Case – When – Then – Else. CREATE OR REPLACE FUNCTION get_case(in p_name varchar(2)) RETURNS varchar AS $$ DECLARE v_name varchar(50); BEGIN CASE WHEN p_name = ‘T’ then v_name:=’The name is TEST’; return v_name; ELSE v_name:=’The name is XYZ’; return v_name; END CASE; END; $$ LANGUAGE ‘plpgsql’; Control structures | IF |… Read More »

Alter table rename table name

PostreSQL tutorial – Rename table Rename name of existing table How to rename the name of existing table. Alter syntax and example. Syntax ALTER TABLE old_table_name RENAME TO new_table_name; Example Rename column ALTER TABLE customers RENAME TO buyers; Alter table | Drop table

Alter table rename column from existing table

PostreSQL tutorial – Rename column Rename column from existing table How to rename column from existing table. Alter syntax and example. Syntax ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name; Create table CREATE TABLE users( userid numeric not null, username varchar(100), userpass varchar(250), usermail varchar(50), address varchar(1000) ); Example Rename column ALTER TABLE users RENAME COLUMN address TO… Read More »

Alter table drop multiple columns from table

PostreSQL tutorial – Drop multiple columns Drop multiple columns from table How to drop multiple columns from existing table. Alter syntax and example. Syntax ALTER TABLE table_name DROP COLUMN column_name, DROP COLUMN column_name RESTRICT; Create table CREATE TABLE users( userid numeric not null default nextval(‘seq_users’::regclass), username varchar(100), userpass varchar(250), usermail varchar(50), address varchar(1000), first_name varchar(100) last_name varchar(100) );… Read More »