Python ->
Some examples of SQL syntax in PostgreSQL

Postgres SQL syntax in some cases is different from MS-SQLServer or MySQL. Here are two examples of simple operations that are surprisingly different:

-- suppose that we have employees table with department number
-- and want to add department name to it as well


CREATE TABLE emp1
(
emp_no integer,
employee_name varchar(60),
dept_no varchar(10)
) WITH (OIDS=FALSE);
ALTER TABLE emp1 OWNER TO postgres;

-- add department name column to employees table
ALTER TABLE emp1 ADD COLUMN dept_name varchar(60);

-- replace nulls by 0
UPDATE emp1 SET dept_no = '0' WHERE dept_no = '' OR dept_no IS NULL;

-- [1] alter column data type (convert column data type) from varchar to int
ALTER TABLE emp1
ALTER COLUMN dept_no TYPE INTEGER USING dept_no::INTEGER;

-- [2] update join between employees and departments tables
UPDATE emp1 AS e
SET dept_name = d.dept_name
FROM dept1 AS d
WHERE e.dept_no = d.dept_no;




sqlexamples.info