invalid input syntax for type timestamp

[guishake]

Hello

I'm having this issue with the folllowing config

pgloader version "3.6.6" compiled with SBCL 2.2.5

MySQL table defintion:

CREATE TABLE `mytable` (
  `last_update` datetime(6) NOT NULL DEFAULT current_timestamp(6),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the pgloader script is quite simple

LOAD DATABASE
FROM mysql://xxx@host/test_pgloader
INTO postgresql://xxx@localhost/mydb;

I get the following message with this script

2022-07-19T15:31:18.562329+01:00 ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "current_timestamp(6)"
QUERY: CREATE TABLE test_pgloader.mytable 
(
  last_update timestamptz not null default 'current_timestamp(6)'
);

I saw the issue #341 which is now closed and reports a similar issue

Any idea how to deal with this ?

Thanks a lot,

[SilentNessProveri]

any news? I have the same problem too

[khanhdb]

Got the same problem. Have you got it solved @SilentNessProveri ?

[linux-root]

@dimitri please help. Here is my situations : pgloader version "3.6.999791d" compiled with SBCL 2.2.10 Table in MariaDB to be migrated CREATE TABLE EXAMPLE( id bigint AUTO_INCREMENT PRIMARY KEY, name varchar(40) NOT NULL UNIQUE, createdAt datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, ) engine=InnoDB DEFAULT CHARSET=utf8; What I got when running migration is the following error:

ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "current_timestamp(3)" QUERY: CREATE TABLE example ( id bigserial not null, name varchar(40) default NULL, created_at timestamptz default 'current_timestamp(3)' )

Actually, when removing the single quote from the line created_at timestamptz default 'current_timestamp(3)' of generated QUERY, I could execute it successfully directly from postgres console

[rekliner]

Having same issue here, not sure why #341 was closed. current_timestamp should not be surrounded by single quotes per postgresql.org devs

It looks like @dimitri left himself a todo note for this in the sql translations. The mysql code should go here.. It searches for current_timestamp() but it's not catching current_timestamp(x) in its filters so it defaults to an unknown parameter and throws the single quotes around it. I've not worked with lisp before but the solution here is to make a PR that accounts for incoming sql with a number as a parameter.

It might be something simple for that line as (cl-ppcre:scan "current_timestamp\(\d?\)" default)))

I can try it out next week when I have time...until then do any LISP programmers want to step up?

[linux-root]

For those who are seeking a temporary workaround, I got it done by dropping the default value of the column and then adding the default value back after the table is migrated. Here is what my pgloader script looks like :

CAST type datetime to timestamptz drop default
AFTER LOAD DO
   $$ alter table my_table alter created_at set default now() $$

[brainbolt]

Same issue here.

pgloader version "3.6.9" 
compiled with SBCL 2.3.4

Migrating a MSSQL timestamp column.

[tconley]

Same issue here when going from MSSQL to PG using version version 3.6.7 at https://colab.research.google.com/github/mansueli/Supa-Migrate/blob/main/Amazon_RDS_to_Supabase.ipynb

2024-05-05T06:16:15.965712Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: 
QUERY: CREATE TABLE dbo.cms_activities 
(
  id              bigserial not null,
  contactid       int default '0',
  organizationid  int default '0',
  staffid         int default '0',
  typeid          int default '0',
  activitydate    timestamptz default CURRENT_TIMESTAMP,
  timestart       timestamptz default '',
  timeend         timestamptz default '',
  taskid          int default '0',
  notes           text default NULL,
  nextaction      text default NULL,
  updatedby       int default '0',
  createdate      timestamptz default CURRENT_TIMESTAMP,
  updatedate      timestamptz default CURRENT_TIMESTAMP,
  duration        real default '0',
  startdate       timestamptz default CURRENT_TIMESTAMP,
  enddate         timestamptz default CURRENT_TIMESTAMP,
  numparticipants int default '0',
  goalactivity    int default '0'
);

[alexanderholder]

This comment helped me with the syntax for the above workaround for other timestamp types https://github.com/dimitri/pgloader/issues/341#issuecomment-2045367908