1

I have one PostgreSQL database. I want to export this PostgreSQL database into MS SQL Server database.

For this, first I backup the PostgreSQL database of schema-only using below command and restore this into SQL Server database. I just replace timestamp without time zone type to datetime in SQL Server.

/opt/netiq/idm/apps/postgresql/bin/pg_dump -U admin --schema-only --no-owner --no-privileges testdata > /home/testdata.sql

Then I try to restore PostgreSQL database data inside SQL Server database table. I backup the PostgreSQL database data by using below command

/opt/netiq/idm/apps/postgresql/bin/pg_dump -U admin --data-only --column-inserts testdata > /home/testdata.sql

When I try to restore data from the above dump I am getting the below error

Conversion failed when converting date and/or time from character string

in below query

INSERT INTO databasechangelog (id, author, filename, dateexecuted, orderexecuted, exectype, md5sum, description, comments, tag, liquibase) VALUES ('3600', 'IDMRBPM', 'DbRenameTables.xml', '2017-09-06 00:36:31.985959+05:30', 83, 'MARK_RAN', '7:d83f1247d179d535dadf892528470df9', 'renameTable', 'Renaming PORTALREGISTRY to PORTALREGISTRY_ORIG', NULL, '3.1.1');

So please help me to fix this.

I have some below query of PostgreSQL database and I want to know that what is the equivalent query for the below in MS SQL Server.

ALTER TABLE ONLY configuration_item ALTER COLUMN id SET DEFAULT nextval('configuration_item_id_seq'::regclass);
ALTER TABLE ONLY securityaccessrights ADD CONSTRAINT accrts_eliid_uk UNIQUE (fwelementiid, permissionid);
ALTER TABLE ONLY portalportlethandles ADD CONSTRAINT portlethandles_pk PRIMARY KEY (portletiid);
CREATE INDEX afcomment_messageid_idx ON afcomment USING btree (messageid);
ALTER TABLE ONLY afactivity ADD CONSTRAINT fk_activity_proc FOREIGN KEY (requestid) REFERENCES afprocess(requestid) ON DELETE CASCADE;
ALTER TABLE ONLY ui_item ADD CONSTRAINT fk_uiitem_citem FOREIGN KEY (id) REFERENCES configuration_item(id);

I am using MS SQL Server 2014 - 12.0.4100.1 (X64) and PostgreSQL 9.3.4

user3441151
  • 1,880
  • 6
  • 35
  • 79
  • What is the `dateexecuted` column's type? It should be `datetimeoffset` to accept values with offsets – Panagiotis Kanavos Oct 24 '17 at 14:25
  • 1
    Every SQL database has it's own dialect of the language, with it's own quirks and non-standard extensions. You're unlikely to be able to move from one DB to another with a simple export=>restore like this. Postgresql will do a few things different from the standard that Sql Server does not, and Sql Server will do some things different from the standard that Postgresql does not. – Joel Coehoorn Oct 24 '17 at 14:25
  • The SQL standard allows levels of compliance and no database offers more than basic level compliance, *especially* for DDL. For example, SQL Server support sequences just like Postgres. SQL Server uses the *standard* syntax, `NEXT VALUE FOR` while PostgreSQL in your sample uses a *custom* syntax, `nextval('somename')` – Panagiotis Kanavos Oct 24 '17 at 14:34
  • 2
    The value `'2017-09-06 00:36:31.985959+05:30'` is a timestamp *with* timezone. It should be mapped to a `datetimeoffset` – Panagiotis Kanavos Oct 24 '17 at 14:37
  • @PanagiotisKanavos Thanks for your reply and I cannot change the type of the column from `datetime` to `datetimeoffset`. I have one query `SELECT pg_catalog.setval('configuration_item_id_seq', 100, true);` What I have to use in SQL Server in place of this, can you please help me out in this. – user3441151 Oct 25 '17 at 06:52
  • @user3441151 you can't *not* change the time to datetimeoffset, unless you want to modify *all* the data to remove the offset. The string you posted is not a datetime value. It's not a datetime value in PostgreSQL either. As for the query you posted, what is it supposed to do? Initialize a sequence? Read about [Sequences in the docs](https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers). This [article by Joe Celko](https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/) is also useful, showing various tricks – Panagiotis Kanavos Oct 25 '17 at 07:23

1 Answers1

0

Just starting by mentioning the documentation I am using:
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
(removed info on insert statement, comment indicates it was incorrect)

Alter Tables

Example from ms sql doc:

ALTER TABLE Person.ContactBackup ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID) ;

Selected query from your list:

ALTER TABLE ONLY ui_item ADD CONSTRAINT fk_uiitem_citem FOREIGN KEY (id) REFERENCES configuration_item(id);

Should be fine if you drop the ONLY from the command, it is implied in both postgres and MS SQL.

Where you have a default value, use DEFAULT in place of SET DEFAULT

When adding an index, for MS SQL they are either CLUSTERED or UNCLUSTERED

For example: CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

Hope this helps.

Tyler
  • 106
  • 1
  • 5
  • 1
    That's *wrong*. SQL Server's `datetimeoffset` type can handle timezone offsets – Panagiotis Kanavos Oct 24 '17 at 14:24
  • Thanks for pointing that out, removed that bit, not sure what the issue is with insert statement. best suggestion I have regarding insert statement is to import as a character string as change the time once the data is imported but maybe someone has a better suggestion? – Tyler Oct 24 '17 at 14:33
  • the issue would be that the field isn't `datetimeoffset`. – Panagiotis Kanavos Oct 24 '17 at 14:35