DtSQL - PostgreSQL Features

 

 
 

 

 
 

Schema

Create Schema

PostgreSQL allows to create a new schema. After entering the schema name, DtSQL can generate and execute the SQL to create the new schema.

Sample : CREATE SCHEMA "TEST_SCHEMA"

Rename Schema

PostgreSQL allows to rename an existing schema. After entering the schema name, DtSQL can generate and execute the SQL to rename the schema.

Sample : ALTER SCHEMA "TEST_SCHEMA" RENAME TO "TESTSCHEMA"

Drop Schema

PostgreSQL allows to drop an existing schema. After entering the schema name, DtSQL can generate and execute the SQL to drop the schema.

Sample : DROP SCHEMA "TEST_SCHEMA"

TOP
 

Table

Create Table

PostgreSQL allows to create a new table. After entering the table name and column information, DtSQL can generate and execute the SQL to create the table.

  • Column Name
  • Column Type
  • Length/Precesion
  • Scale (decimal)
  • Default Value
  • Nullable
  • Primary Key
  • Unique

Sample :
CREATE TABLE "TEST_SCHEMA"."TEST_TABLE" (
a int8 NOT NULL DEFAULT 1,
b char ( 12 ) NOT NULL DEFAULT 'abc',
c numeric ( 12, 2 ) ,

PRIMARY KEY ( a, b ),
UNIQUE ( b,c )
)

Rename Table

PostgreSQL allows to rename an existing table. After entering the table name, DtSQL can generate and execute the SQL to rename the table.

Sample : RENAME TABLE "TEST_SCHEMA"."TEST_TABLE" TO TESTTABLE

Truncate Table

PostgreSQL does not allows to truncate an table and deleting table is used instead. After entering the table name, DtSQL can delete all data in the table.

Sample : TRUNCATE TABLE "TEST_SCHEMA"."TEST_TABLE"

Drop Table

PostgreSQL allows to drop an existing table. After entering the table name, DtSQL can generate and execute the SQL to drop the table.

Sample : DROP TABLE "TEST_SCHEMA"."TEST_TABLE"

TOP
 

Column

Add Column

PostgreSQL allows to add a new column to an existing table. After selecting the table and entering column information, DtSQL can generate and execute the SQL to add the new column to the table.

  • Column Name
  • Column Type
  • Length/Precesion
  • Scale (decimal)
  • Default Value
  • Nullable

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
ADD
d varchar(20) NOT NULL DEFAULT 'def'

Change Data Type

PostgreSQL allows to change the column data. After selecting the new data type, DtSQL can generate and execute the SQL to change the column data type.

Sample :
ALTER TABLE "TEST_SCHEMA"."TEST_TABLE"
ALTER COLUMN F TYPE INT8

Drop Column

PostgreSQL allows to delete an existing column. After selecting the column, DtSQL can generate and execute the SQL to delete the column.

Sample :
ALTER TABLE "TEST_SCHEMA"."TEST_TABLE"
DROP COLUMN F

TOP
 

View

Create View

PostgreSQL allows to create a new view. After entering the view name and select SQL, DtSQL can generate and execute the SQL to create the view.

Sample :
CREATE VIEW "TEST_SCHEMA"."TEST_VIEW"
AS
SELECT * FROM "TEST_SCHEMA"."TEST_TABLE"

Rename View

PostgreSQL allows to rename an existing view. After selecting the view name, DtSQL can generate and execute the SQL to rename the view.

Sample : ALTER VIEW "TEST_SCHEMA"."TEST_VIEW" RENAME TO TESTVIEW

Drop View

PostgreSQL allows to drop an existing view. After selecting the view name, DtSQL can generate and execute the SQL to delete the view.

Sample : DROP VIEW "TEST_SCHEMA"."TEST_VIEW"

TOP
 

Index

Create Index

PostgreSQL allows to create a new index. After selecting the table columns and entering the view name, DtSQL can generate and execute the SQL to create the index.

Sample :
CREATE UNIQUE INDEX"TEST_INDEX"
ON "TEST_SCHEMA"."TEST_TABLE" ( A, B DESC, C DESC )

Rename Index

PostgreSQL allows to rename an existing index. After selecting the index and entering the new index name, DtSQL can generate and execute the SQL to rename the index.

Sample : RENAME INDEX "TEST_SCHEMA"."TEST_INDEX" TO TESTINDEX

Drop Index

PostgreSQL allows to drop an existing index. After selecting the index name, DtSQL can generate and execute the SQL to delete the index.

Sample : DROP INDEX "TEST_SCHEMA"."TEST_INDEX"

TOP
 

Trigger

Create Trigger

PostgreSQL allows to create a new trigger. After entering the trigger name and trigger actions, DtSQL can generate and execute the SQL to create the trigger.

Sample :
CREATE TRIGGER "TEST_SCHEMA"."TEST_TRIGGER"
AFTER INSERT
ON "TEST_SCHEMA"."TEST_TABLE"
FOR EACH ROW
EXECUTE PROCEDURE TRIG_FUNC()

Rename Trigger

PostgreSQL allows to rename an existing trigger. After selecting the trigger name, DtSQL can generate and execute the SQL to rename the trigger.

Sample : ALTER TRIGGER "TEST_TRIGGER" ON "TEST_SCHEMA"."TEST_TABLE"
RENAME TO TSETTRIGGER

Drop Trigger

PostgreSQL allows to drop an existing trigger. After selecting the trigger name, DtSQL can generate and execute the SQL to delete the trigger.

Sample : DROP TRIGGER "TEST_SCHEMA"."TEST_TRIGGER"

TOP
 

Constraint

Create Primary Key

PostgreSQL allows to create a primary key for a table. After selecting the table columns, DtSQL can generate and execute the SQL to create the primary key.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
ADD
PRIMARY KEY ( B, C )

Create Foreign Key

PostgreSQL allows to create a foreign key for a table to reference to another table. After selecting the table columns and referenced table columns, DtSQL can generate and execute the SQL to create the foreign key.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
ADD
FOREIGN KEY ( A, C )
REFERENCES "TEST_SCHEMA"."TEST_TB" (B, A )

Create Check Constraint

PostgreSQL allows to create a check constraint for a table column. After entering the check constraint name and check action, DtSQL can generate and execute the SQL to create the check constraint.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
ADD CONSTRAINT "TEST_CHECK"
CHECK ( C > 10 )

Create Unique Constraint

PostgreSQL allows to create an unique constraint for a table columns. After entering the unique constraint name and selecting table columns, DtSQL can generate and execute the SQL to create the unique constraint.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
ADD CONSTRAINT "TEST_UNIQUE"
UNIQUE ( B, C )

Drop Primary Key

PostgreSQL allows to drop an existing primary key. After selecting the primary key, DtSQL can generate and execute the SQL to delete the primary key.

Sample : ALTER TABLE "TEST_SCHEMA"."TEST_TABLE" DROP CONSTRAINT TEST_TABLE_PKEY

Drop Foreign Key

PostgreSQL allows to drop an existing foreign key. After selecting the foreign key, DtSQL can generate and execute the SQL to delete the foreign key.

Sample :
ALTER TABLE "TEST_SCHEMA"."TEST_TABLE"
DROP FOREIGN KEY "TEST_TABLE_FKEY"

Drop Check Constraint

PostgreSQL allows to drop an existing check constraint. After selecting the check constraint, DtSQL can generate and execute the SQL to delete the check constraint.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
DROP CONSTRAINT "TEST_CHECK"

Drop Unique Constraint

PostgreSQL allows to drop an existing unique constraint. After selecting the unique constraint, DtSQL can generate and execute the SQL to delete the unique constraint.

Sample :
ALTER TABLE
"TEST_SCHEMA"."TEST_TABLE"
DROP CONSTRAINT "TEST_UNIQUE"

TOP
 

Function

Create Function

PostgreSQL allows to create functions. After entering the procedure name, parameter names and function body, DtSQL can generate and execute the SQL to create the function.

Sample :
CREATE OR REPLACE FUNCTION TEST_SCHEMA.TEST_FUNC
( IN PARAM1 INT8, IN PARAM2 INT8, IN PARAM3 DATE )
RETURNS
AS $dtsql$
select $1 + $2;
$dtsql$
LANGUAGE SQL;

Rename Function

PostgreSQL allows to rename an existing function. After selecting the function, DtSQL can generate and execute the SQL to rename the function.

Sample : ALTER FUNCTION "TEST_SCHEMA"."TEST_FUNC"
( IN PARAM1 INT8, IN PARAM2 INT8, IN PARAM3 DATE )
RENAME TO TESTFUNC

Drop Function

PostgreSQL allows to drop an existing function. After selecting the function, DtSQL can generate and execute the SQL to delete the function.

Sample : DROP FUNCTION "TEST_SCHEMA"."TEST_FUNC"
(IN PARAM1 INT8, IN PARAM2 INT8, IN PARAM3 DATE )

TOP
 

Sequence

Create Sequence

PostgreSQL allows to create sequences. After entering the sequence parameters, DtSQL can generate and execute the SQL to create the sequence.

Sample :
CREATE SEQUENCE "TEST_SCHEMA"."TEST_SEQ"
INCREMENT BY 2
MINVALUE 1
MAXVALUE 10000
START WITH 10
CACHE 20
CYCLE

Rename Sequence

PostgreSQL allows to rename an existing sequence. After selecting the sequence, DtSQL can generate and execute the SQL to rename the sequence.

Sample : ALTER SEQUENCE "TEST_SCHEMA"."TEST_SEQ" RENAME TO TESTSEQ

Drop Sequence

PostgreSQL allows to drop an existing sequence. After selecting the sequence, DtSQL can generate and execute the SQL to delete the sequence.

Sample : DROP SEQUENCE "TEST_SCHEMA"."TEST_SEQ"

 

TOP

Copyright DigerTech Inc