postgresql_testing.sql

testing=> CREATE TABLE products(
testing(>     id SERIAL PRIMARY KEY NOT NULL,
testing(>     name VARCHAR(255) NOT NULL,
testing(>     quantity INT NOT NULL
testing(> );
CREATE TABLE

testing=> INSERT INTO products(name) VALUES('First product');
ERROR:  null value in column "quantity" violates not-null constraint
DETAIL:  Failing row contains (1, First product, null).

testing=> INSERT INTO products(name, quantity) VALUES('First product', 100);
INSERT 0 1

testing=> SELECT * FROM products;
 id |     name      | quantity 
----+---------------+----------
  2 | First product |      100
(1 row)

testing=> ALTER TABLE products ADD COLUMN price DECIMAL(6,2) NOT NULL;
ERROR:  column "price" contains null values

testing=> ALTER TABLE products ADD COLUMN price DECIMAL(6,2);
ALTER TABLE

testing=> UPDATE products SET price=100.00;
UPDATE 1

testing=> ALTER TABLE products ALTER COLUMN price SET NOT NULL;
ALTER TABLE

testing=> SELECT * FROM products;
 id |     name      | quantity | price  
----+---------------+----------+--------
  2 | First product |      100 | 100.00
(1 row)

testing=> ALTER TABLE products ALTER COLUMN price TYPE numeric(2,2);
ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 2 must round to an absolute value less than 1.