Add timestamp fields to ogr2ogr import into Postgis

Question

I managed to create a model/schema and insert geo-Points into Postgis using sequelize. Since I have a lot of points (>100K) I am longing back to the old way I used to import, using ogr2ogr (gdal), which was much faster (almost instant instead of >20 minutes). As I would like to continue to work with sequelize after the initial import I still want sequelize to create the model/schema upfront, but then do import with ogr2ogr, and then continue CRUD with sequelize.

Here I found this fragment “[….] One way to get around this is to create your table structures before hand and use OGR2OGR to just load the data.” Which gave me the idea that this could work for Postgres/Postgis as well.

Sequelize creates timestamp columns for createdAt and updatedAt, which I like. But when I use ogr2ogr I get “null value in column "createdAt" violates not-null constraint” as loginfo.

Based on this slightly similar issue I tried to add a createdAt column by adding an -sql option:

ogr2ogr -f PostgreSQL PG:"host='0.0.0.0' user='root' port='5432' dbname='db' password='pw'" /home/user/geojsonImportfile.json -nln "DataPoints" -a_srs EPSG:4326 -sql "SELECT url, customfield, wkb_geometry, CURRENT_TIMESTAMP as createdAt FROM '/home/usr/geojsonImportfile.json'" -dialect 'PostgreSQL'

The error I get when running this is:

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected end of string, expecting '.'. Occurred around :
home/user/geojsonImportfile0.json'

Besides my lack of SQL knowledge I am not sure if this can work at all.

How can I solve this, i.e. make the import with ogr2ogr but keep the timestamp columns?

Answer 1

When you create a table with sequelize.define, createdAt and updatedAt columns are created automatically as timestamp with time zone NOT NULL.

But you can rule not-null constraint in your sequelize definition script:

const Mytable = sequelize.define('mytable', {
    id: {type: Sequelize.INTEGER, primaryKey: true},
    createdAt: {type: Sequelize.DATE, validate: {notNull:false}}
});

Then table is created like :

CREATE TABLE mytables
(
  id integer NOT NULL,
  "createdAt" timestamp with time zone,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT mytables_pkey PRIMARY KEY (id)
)

http://docs.sequelizejs.com/manual/tutorial/models-definition.html#configuration

Answer 2

@JGH following your suggestion it makes sense to have a default timestamp. I can already set this up using sequelize, as discussed here:

var user = sequelize.define('user', {
  createdAt: {
    type: DataTypes.DATE,
    field: 'beginTime',
    defaultValue: sequelize.literal('NOW()')
  }
}, {
  timestamps: true,

});