current_timestamp does not exist in PostgreSQL?

Question

current_timestamp does not exist in PostgreSQL?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a database that is about Heroku. A field is filled automatically with the date. I try to use current_timestamp , but it seems that it does not work.

Here is the schema of the database.

drop table if exists users;
create table users (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar,
  email varchar,
  username varchar,
  password varchar,
  register_date CURRENT_TIMESTAMP() not null
);

Here is the query:

cur.execute("INSERT INTO users(name, email, username, password) VALUES(%s, %s, %s, %s)", (name, email, username, password))

And here is the error:

ERROR:  syntax error at or near "CURRENT_TIMESTAMP"
LINE 7:   register_date CURRENT_TIMESTAMP() not null

postgresql

asked by ThePassenger 03.07.2018 в 16:18

source

2 answers

1

As indicated by a syntax error in the data type register_date and in the CURRENT_TIMESTAMP () remove the parentheses () and you need to add DEFAULT before CURRENT_TIMESTAMP as follows:

drop table if exists users;
create table users (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar,
  email varchar,
  username varchar,
  password varchar,
  register_date timestamp DEFAULT CURRENT_TIMESTAMP not null
);

answered by 03.07.2018 в 16:47

Date query about BD Access VB.NET DataGridView change image style (tile, stretch, center, zoom)

score 2 · Accepted Answer

CURRENT_TIMESTAMP Yes exists in PostgreSQL , what is in your query is a syntax error because:

You are not specifying the data type of the register_date column
you are writing CURRENT_TIMESTAMP with parentheses
you're not using DEFAULT
you are not creating the primary key well

If you write the query like this, the error should disappear:

drop table if exists users;
create table users (
  id             bigserial       primary key,
  name           varchar (150),
  email          varchar (150),
  username       varchar (50),
  password       varchar (50),
  register_date  timestamp       default current_timestamp not null
);

Something else: It's a good idea to give the columns varchar a size, otherwise you'll create them as if they were text , which is not good. You can read this answer , in which I have translated from the documentation how the varchar columns work when not specified a size in them.