PostgreSQL sheets

1. Alter Table

* Add a New Column

ALTER TABLE table_name
ADD column_name datatype;

* Modify Column Data Type

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE new_datatype;

* Rename a Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

* Rename a Column

ALTER TABLE table_name
RENAME COLUMN old_column_name
TO new_column_name;

* Drop a Column

ALTER TABLE table_name
DROP COLUMN column_name;

2. Constraints

* Add a Primary Key

ALTER TABLE table_name
ADD CONSTRAINT table_name_pkey
PRIMARY KEY (column_name);

* Add a Unique Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column_name);

* Add a Foreign Key Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES referenced_table(referenced_column);

* Remove a Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

3. Default Value

* Set Default Value for a Column

ALTER TABLE table_name
ALTER COLUMN column_name
SET DEFAULT default_value;

* Remove Default Value

ALTER TABLE table_name
ALTER COLUMN column_name
DROP DEFAULT;

4. Check Constraints

* Add a Check Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);

* Remove a Check Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

5. Sequences (Serial)

* Add a Serial Column

ALTER TABLE table_name
ADD column_name serial;

6. Indexes

* Add an Index

CREATE INDEX index_name
ON table_name (column_name);

* Remove an Index

DROP INDEX index_name;

7. Create a Function

    CREATE OR REPLACE FUNCTION function_name (
    parameter_name parameter_type,
    ...
    )
    RETURNS return_type AS $$
    DECLARE
        variable_name data_type;
    BEGIN
      -- Function logic here
        -- Use variables and parameters as needed
    RETURN result;
    END;
    $$ LANGUAGE plpgsql;

8. Create a Procedure

    CREATE OR REPLACE FUNCTION procedure_name (
    parameter_name parameter_type,
    ...
    ) AS $$
    DECLARE
        variable_name data_type;
    BEGIN
        -- Procedure logic here
        -- Use variables and parameters as needed
    END;
        $$ LANGUAGE plpgsql;

9. Call a Function/Procedure

    SELECT function_name(parameter_value, ...);