ERROR Database error 22P02: invalid input syntax for integer: "create default [zero] as 0

[gunces]

Hello,

I try to migrate from SQL Server to PostgreSQL. Some columns in SQL Server tables has default value like bellow:

CREATE TABLE dbo.tbl_asx_work_order
( col1                    int default 'create default [zero] as 0',
  col2             smallint default 'create default [zero] as 0')

When I try to run pgloader, I get some error because of 'create default [zero] as 0'

2019-10-31T10:53:18.120000Z ERROR Database error 22P02: invalid input syntax for integer: "create default [zero] as 0

Is there any way to fix this error?

[dimitri]

Do you have a MS SQL documentation link that explains what this syntax is supposed to mean? Do you have more information regarding how to convert that to Postgres?

[fpuga]

I have a similar issue here

2020-09-18T15:34:00.475000Z ERROR Database error 22P02: invalid input syntax for integer: "datepart(year,getdate())"
QUERY: CREATE TABLE dbo.cop_parametrosligacao 
(
  id                      uuid not null default uuid_generate_v4(),
  ano                     smallint not null default 'datepart(year,getdate())',
);

[dimitri]

Hi @fpuga ; it's not the same problem as above. In your case you should use a per-column user defined cast rule and drop the default value. Then re-install a Postgres version of it...

[pbjorklund]

I'm having the same issue I think

2022-11-03T07:26:26.657405-07:00 ERROR Database error 22P02: invalid input syntax for type double precision: "NEXT VALUE FOR [ActivitySequence]"
QUERY: CREATE TABLE public.activities
(
  id                  uuid not null,
  label               text default NULL,
  placeholder         text default NULL,
  type                int not null,
  index               float not null default 'NEXT VALUE FOR [ActivitySequence]',
  step_id             uuid not null,
  is_required         boolean not null default 'f',
  description         text default NULL,
  uploaded_file_id    uuid default NULL,
  activity_version_id uuid default NULL,
  accept_multiple     boolean not null default NULL,
  custom_list_id      uuid default NULL
);
2022-11-03T07:26:26.657486-07:00 FATAL Failed to create the schema, see above.

https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16