You insert NULL value by typing NULL:

INSERT INTO table(number1,number2,number3) VALUES (1,NULL,3);

If you have a variable and when that variable is empty you want to insert a NULL value you can use NULLIF with the variable enclosed in single quotes to prepare for that (this is somewhat dirty solution as you have to treat the variable as an empty string and then convert it to integer):

INSERT INTO table(number1,number2,number3) VALUES (1,NULLIF('$var','')::integer,3);

To insert null values to the database you have two options:

1. omit that field from your INSERT statement, or
2. use None

Also: To guard against SQL-injection you should not use normal string interpolation for your queries.

You should pass two (2) arguments to execute(), e.g.:

mycursor.execute("""INSERT INTO products 
                    (city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s)""", 
                 (city_id, product_id, quantity, price))

Alternative #2:

user_id = None
mycursor.execute("""INSERT INTO products 
                    (user_id, city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s, %s)""", 
                 (user_id, city_id, product_id, quantity, price))

'NULL' is not the same as NULL, the first one is an string just like any other string, so your constraint (NOT NULL) won't work.

I think you are working with a programming language (like PHP or Java) that you put your value inside a string with a variable like

INSERT INTO originators(originator, id, regexp) 
VALUES ('$variable', $id, $regexp); 

so in this case you have to correct your code to prevent the quotes, or if you can't do that you constraint should be CHECK(upper(originator) != 'NULL').

A trigger is the right workaround if your problem can't be fixed.

You stated:

The problem is that the server produces queries with explicit null values if not given other instructions. I have no way of changing the query being sent from the server to pg.

The right solution would be to "give other instructions". Or replace that "server" of yours with decent software if it's incapable (which I doubt).

Additionally, define both columns NOT NULL to raise an exception if the error should still happen (unless you need to allow NULL values).

If you have to add that trigger after all, make it efficient:

CREATE OR REPLACE FUNCTION force_user_defaults()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.date_created := now();
   NEW.edited_by    := current_user();
   RETURN NEW;
END
$func$;

CREATE TRIGGER test_table_before_insert
BEFORE INSERT ON test_table
FOR EACH ROW
WHEN (NEW.date_created IS NULL OR NEW.edited_by IS NULL)  -- !
EXECUTE FUNCTION force_user_defaults();

The point being to only even call the trigger function if violating NULL values are passed. Note then WHEN clause in CREATE TRIGGER - which only makes sense if those NULL values are the exception rather than the rule.

About EXECUTE FUNCTION:

• Trigger uses a procedure or a function?

Nonsense. Your statement is syntactically correct.
None of the suggestions here are necessary. The error you report should not occur.

Also, SELECT INTO (like you wrote in error) is an unrelated command - the use of which is discouraged in favor of CREATE TABLE AS.

Typically, you can just omit columns that shall be NULL from the target list - unless a different default value is set for the column. But it's typically bad style to omit the target column list altogether (exceptions apply).

INSERT INTO table_2 (a, b)
SELECT a, b
FROM   table_1;

If column table_2.c has no different DEFAULT value in the table definition, it defaults to NULL. To be precise:

• How to use default value of data type as column default?

Shorter; but there is nothing wrong with your original query. In fact, it's the best way.