Inserting NULL value to TIMESTAMP fields in postgreSQL

Question

I have the following DB schema in MySQL, which I have replicated in PostgreSQL using an ORM:

CREATE TABLE IF NOT EXISTS users (
id               BIGINT NOT NULL DEFAULT NEXTVAL ('users_seq'),
email            VARCHAR(255) NOT NULL DEFAULT '',
password         VARCHAR(255) NOT NULL DEFAULT '',
last_name        VARCHAR(255) NOT NULL DEFAULT '',
first_name       VARCHAR(255) NOT NULL DEFAULT '',
created          TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated          TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)

);

And I'm trying to execute the following query

INSERT INTO users (id, email, password,first_name, last_name, created, updated)
VALUES (1, '[email protected]', 'pass', 'user', 'user', NULL, NULL);

This results in an error

ERROR:  null value in column "created" violates not-null constraint

Expected behaviour is to have the current timestamp in case the value is NULL, and it works in MySQL

I'm using PostgreSQL 10. Am I missing any configuration or is this not supported in Postgres?

remove postgres from tags please. if you want default value, skip the column from the list - if you explicitly specify NULL - it will inert NULL, not DEFAULT — Vao Tsun
– Vao Tsun, Commented Oct 16, 2017 at 8:48
allow null for created and updated fields, you are trying to insert null into non nullable fields. Only default will will insert if your insert query don't have this field — Bharat
– Bharat, Commented Oct 16, 2017 at 8:49
@VaoTsun why remove postgres tag?. This is supported by mysql. This query is generated from an ORM library. — Anuruddha
– Anuruddha, Commented Oct 16, 2017 at 8:55
Vao Tsun's confusion may be that you have said "I have this Postgres table definition", but the definition you gave is in MySQL syntax (backtick-quoted identifiers, ENGINE = InnoDB, etc). I have edited the question to say that this is the MySQL version you have replicated, but you might want to edit to provide the actual Postgres CREATE TABLE statement instead. — IMSoP
– IMSoP, Commented Oct 16, 2017 at 9:02

IMSoP · Accepted Answer · 2017-10-16 08:56:04Z

NULL is not the same as "default".

When you specify NULL in your list of values to insert, Postgres will try to insert a NULL into that column of the table. If there is a constraint which prevents that from happening, such as a NOT NULL specifier on the column, the insert will fail.

If, however, you do not specify a value for that column, Postgres will insert the default value for that column, or NULL if no default is specified.

Consequently, since you have DEFAULT values on both of those columns you can write this:

INSERT INTO users (id, email, password,first_name, last_name)
VALUES (1, '[email protected]', 'pass', 'user', 'user');

Which will effectively mean the same as this:

INSERT INTO users (id, email, password,first_name, last_name, created, updated)
VALUES (1, '[email protected]', 'pass', 'user', 'user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Note that it is perfectly legal to have a column which both accepts NULL and has a default value. If attempting to insert a NULL always inserted the default value, this would be impossible (or, at least, unnecessarily complicated).

user3821178 · Accepted Answer · 2021-12-07 00:16:38Z

1

In case you dont want to set the default to current timestamp, you can consider using -infinity or infinity that way you can always find the data that had missing datetimes

answered Dec 7, 2021 at 0:16

user3821178

3736 silver badges16 bronze badges

Collectives™ on Stack Overflow

Inserting NULL value to TIMESTAMP fields in postgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related