Thread: converting microsoft sql server 2000 sql-code for postgresql 7.2.1
Hello all, I a m a new member of the list and at the moment , I am studiing informatica: sql. At the workplace, we use microsoft sql server 2000. At home, I use postgresql 7.2.1 and now I would import the data of the database at the workplace into the postgresql environment at home. I have paste a little part of the sql-code to create a table in a database called "tennisclub". To execute the code bellow with pgsql, I do the following steps: pgsql tennisclub \e file_with_sql_code.sql The contens of the file file_with_sql_code.sql is as follows: CREATE TABLE SPELERS (SPELERSNR SMALLINT NOT NULL, NAAM CHAR(15) NOT NULL, VOORLETTERS CHAR(3) NOT NULL, VOORVOEGSELS CHAR(7) , GEB_DATUM datetime , GESLACHT CHAR(1) NOT NULL, JAARTOE SMALLINT NOT NULL, STRAAT CHAR(15) NOT NULL, HUISNR CHAR(4) , POSTCODE CHAR(6) , PLAATS CHAR(10) NOT NULL, TELEFOON CHAR(10) , BONDSNR CHAR(4) , PRIMARY KEY (SPELERSNR) ); INSERT INTO SPELERS VALUES ( 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', '80', '1234KK', 'Den Haag', '070-476537', '8467' ); INSERT INTO SPELERS VALUES ( 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', '23', '4444LJ', 'Rijswijk', '070-368753', '1124' ); INSERT INTO SPELERS VALUES ( 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', '16a', '1812UP', 'Den Haag', '070-353548', '1608' ); INSERT INTO SPELERS VALUES ( 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', '43', '3575NH', 'Den Haag', '070-237893', '2411' ); INSERT INTO SPELERS VALUES ( 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', '804', '8457DK', 'Zoetermeer', '079-234857', '2513' ); INSERT INTO SPELERS VALUES ( 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', '65', '9437AO', 'Zoetermeer', '079-987571', '7060' ); INSERT INTO SPELERS VALUES ( 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', '39', '9758VB', 'Den Haag', '070-347689', NULL ); INSERT INTO SPELERS VALUES ( 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', '16', '4377CB', 'Den Haag', '070-473458', '6409' ); INSERT INTO SPELERS VALUES ( 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', '78', '9629CD', 'Den Haag', '070-393435', NULL ); INSERT INTO SPELERS VALUES ( 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', '8', '6392LK', 'Rotterdam', '010-548745', '1319' ); INSERT INTO SPELERS VALUES ( 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', '4', '6584WO', 'Rijswijk', '070-458458', '2983' ); INSERT INTO SPELERS VALUES ( 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', '80', '6494SG', 'Den Haag', '070-494593', '6524' ); INSERT INTO SPELERS VALUES ( 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', '10', '1294QK', 'Leiden', '010-659599', NULL ); INSERT INTO SPELERS VALUES ( 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', '33a', '5746OP', 'Voorburg', '070-867564', NULL ); This code doesn't work. Can someone tell me how I can adjust the syntax of the code and in global: how can I convert sql-code , for microsoft sql server 2000, to sql-code for postgresql? Thanks in advance best regards William Windels
> Can someone tell me how I can adjust the syntax of the code and in global: > how can I convert sql-code , for microsoft sql server 2000, to sql-code for > postgresql? > Try, http://techdocs.postgresql.org/techdocs/sqlserver2pgsql.php regards, bhuvaneswaran
On Tuesday 28 January 2003 10:08 am, william windels wrote: A lot of SQL! Instead of spending a lot of time trying to figure out what's wrong with your code, here's a couple of links that should help you: The most obvious is the PostgreSQL documentation which can be found in the doc directory of your PostgreSQL installation (usually /usr/local/pgsql/doc if you're using Linux), or on the Internet at: http://www.postgresql.org/ Then there's a book online - Practical PostgreSQL: http://www.commandprompt.com/ppbook/book1.htm Geraint.
Re: converting microsoft sql server 2000 sql-code for postgresql 7.2.1
From
"Rajesh Kumar Mallah."
Date:
Only 1 small change makes it acceptable to pgsql. change datetime to timestamp . regds mallah. On Tuesday 28 January 2003 03:38 pm, william windels wrote: > Hello all, > > I a m a new member of the list and at the moment , I am studiing > informatica: sql. > > At the workplace, we use microsoft sql server 2000. > At home, I use postgresql 7.2.1 and now I would import the data of the > database at the workplace into the postgresql environment at home. > > I have paste a little part of the sql-code to create a table in a database > called "tennisclub". > > To execute the code bellow with pgsql, I do the following steps: > > pgsql tennisclub > \e file_with_sql_code.sql > > The contens of the file file_with_sql_code.sql is as follows: > > CREATE TABLE SPELERS > > (SPELERSNR SMALLINT NOT NULL, > > NAAM CHAR(15) NOT NULL, > > VOORLETTERS CHAR(3) NOT NULL, > > VOORVOEGSELS CHAR(7) , > > GEB_DATUM datetime , > > GESLACHT CHAR(1) NOT NULL, > > JAARTOE SMALLINT NOT NULL, > > STRAAT CHAR(15) NOT NULL, > > HUISNR CHAR(4) , > > POSTCODE CHAR(6) , > > PLAATS CHAR(10) NOT NULL, > > TELEFOON CHAR(10) , > > BONDSNR CHAR(4) , > > PRIMARY KEY (SPELERSNR) ); > > INSERT INTO SPELERS VALUES ( > > 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', > > '80', '1234KK', 'Den Haag', '070-476537', '8467' > > ); > > INSERT INTO SPELERS VALUES ( > > 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', > > '23', '4444LJ', 'Rijswijk', '070-368753', '1124' > > ); > > INSERT INTO SPELERS VALUES ( > > 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', > > '16a', '1812UP', 'Den Haag', '070-353548', '1608' > > ); > > INSERT INTO SPELERS VALUES ( > > 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', > > '43', '3575NH', 'Den Haag', '070-237893', '2411' > > ); > > INSERT INTO SPELERS VALUES ( > > 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', > > '804', '8457DK', 'Zoetermeer', '079-234857', '2513' > > ); > > INSERT INTO SPELERS VALUES ( > > 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', > > '65', '9437AO', 'Zoetermeer', '079-987571', '7060' > > ); > > INSERT INTO SPELERS VALUES ( > > 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', > > '39', '9758VB', 'Den Haag', '070-347689', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', > > '16', '4377CB', 'Den Haag', '070-473458', '6409' > > ); > > INSERT INTO SPELERS VALUES ( > > 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', > > '78', '9629CD', 'Den Haag', '070-393435', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', > > '8', '6392LK', 'Rotterdam', '010-548745', '1319' > > ); > > INSERT INTO SPELERS VALUES ( > > 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', > > '4', '6584WO', 'Rijswijk', '070-458458', '2983' > > ); > > INSERT INTO SPELERS VALUES ( > > 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', > > '80', '6494SG', 'Den Haag', '070-494593', '6524' > > ); > > INSERT INTO SPELERS VALUES ( > > 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', > > '10', '1294QK', 'Leiden', '010-659599', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', > > '33a', '5746OP', 'Voorburg', '070-867564', NULL > > ); > > > > This code doesn't work. > > > > Can someone tell me how I can adjust the syntax of the code and in global: > how can I convert sql-code , for microsoft sql server 2000, to sql-code for > postgresql? > > > > Thanks in advance > > > > best regards > > William Windels > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- -------------------------------------------- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Hi You should use "date" instead of "datetime" since the data is only a date and a "date" type uses less storage than a "timestamp". For time only data use type "time" or "timetz" if you want the timezone as well. For data with a date and time use "timestamp" or "timestamptz" if you want to include the timezone as well. I hope this is helpful. There are other time based data types as well but you should read the documentation to determine when it is best to use them. One thing I really like about PostgreSQL is the variety of data types and functions for special operations on them. ## I believe the information below is correct. If you go through the documentation you can also find out how to CREATE your own data TYPE to allow more direct use of non-PostgreSQL data types. Here is an example that will allow input of any "datetime" data into PostgreSQL : CREATE TYPE datetime AS (datetime timestamptz); Then any time,date or date and time data can be input as type datetime. Without having to convert your tables to rename the "datetime" type. Guy Rajesh Kumar Mallah. wrote: > Only 1 small change makes it acceptable to pgsql. > > change datetime to timestamp . > > > regds > mallah. > > On Tuesday 28 January 2003 03:38 pm, william windels wrote: > >>Hello all, >> >>I a m a new member of the list and at the moment , I am studiing >>informatica: sql. >> >>At the workplace, we use microsoft sql server 2000. >>At home, I use postgresql 7.2.1 and now I would import the data of the >>database at the workplace into the postgresql environment at home. >> >>I have paste a little part of the sql-code to create a table in a database >>called "tennisclub". >> >>To execute the code bellow with pgsql, I do the following steps: >> >>pgsql tennisclub >>\e file_with_sql_code.sql >> >>The contens of the file file_with_sql_code.sql is as follows: >> >>CREATE TABLE SPELERS >> >>(SPELERSNR SMALLINT NOT NULL, >> >>NAAM CHAR(15) NOT NULL, >> >>VOORLETTERS CHAR(3) NOT NULL, >> >>VOORVOEGSELS CHAR(7) , >> >>GEB_DATUM datetime , >> >>GESLACHT CHAR(1) NOT NULL, >> >>JAARTOE SMALLINT NOT NULL, >> >>STRAAT CHAR(15) NOT NULL, >> >>HUISNR CHAR(4) , >> >>POSTCODE CHAR(6) , >> >>PLAATS CHAR(10) NOT NULL, >> >>TELEFOON CHAR(10) , >> >>BONDSNR CHAR(4) , >> >>PRIMARY KEY (SPELERSNR) ); >> >>INSERT INTO SPELERS VALUES ( >> >>6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', >> >>'80', '1234KK', 'Den Haag', '070-476537', '8467' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', >> >>'23', '4444LJ', 'Rijswijk', '070-368753', '1124' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', >> >>'16a', '1812UP', 'Den Haag', '070-353548', '1608' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', >> >>'43', '3575NH', 'Den Haag', '070-237893', '2411' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', >> >>'804', '8457DK', 'Zoetermeer', '079-234857', '2513' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', >> >>'65', '9437AO', 'Zoetermeer', '079-987571', '7060' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', >> >>'39', '9758VB', 'Den Haag', '070-347689', NULL >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', >> >>'16', '4377CB', 'Den Haag', '070-473458', '6409' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', >> >>'78', '9629CD', 'Den Haag', '070-393435', NULL >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', >> >>'8', '6392LK', 'Rotterdam', '010-548745', '1319' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', >> >>'4', '6584WO', 'Rijswijk', '070-458458', '2983' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', >> >>'80', '6494SG', 'Den Haag', '070-494593', '6524' >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', >> >>'10', '1294QK', 'Leiden', '010-659599', NULL >> >>); >> >>INSERT INTO SPELERS VALUES ( >> >>95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', >> >>'33a', '5746OP', 'Voorburg', '070-867564', NULL >> >>); >> >> >> >>This code doesn't work. >> >> >> >>Can someone tell me how I can adjust the syntax of the code and in global: >>how can I convert sql-code , for microsoft sql server 2000, to sql-code for >>postgresql? >> >> >> >>Thanks in advance >> >> >> >>best regards >> >>William Windels >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
--- william windels <william.windels@pandora.be> wrote: > how can I convert sql-code , for microsoft sql > server 2000, to sql-code for > postgresql? > In addition to the suggestions given in other replies, have a look at PGAdminII: http://pgadmin.postgresql.org That is an excellent utility, and comes with a database migration utility (separate download) that will migrate MS SQL Server databases. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Guy Fraser <guy@incentre.net> writes:
> If you go through the documentation you can also find out how to CREATE your
> own data TYPE to allow more direct use of non-PostgreSQL data types. Here is
> an example that will allow input of any "datetime" data into PostgreSQL :
> CREATE TYPE datetime AS (datetime timestamptz);
I think what you probably really want is
CREATE DOMAIN datetime AS timestamptz;
or more SQL-spec-compliantly
CREATE DOMAIN datetime AS timestamp with time zone;
which essentially makes datetime a direct alias for timestamptz. The
CREATE TYPE approach makes a rowtype containing one timestamptz column,
which isn't really going to act the way you want --- for one thing,
none of the predefined functions and operators for type timestamptz
will accept it. With the DOMAIN approach, they will.
regards, tom lane
What about a UNIQUEIDENTIFIER type? Is the only way casting it to a CHAR(38)? (Create a domain for it) And does the performance suffer if I do the Domain/create my own data type tricks? Thanks! Wei ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Guy Fraser" <guy@incentre.net> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, January 28, 2003 8:55 PM Subject: Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql > Guy Fraser <guy@incentre.net> writes: > > If you go through the documentation you can also find out how to CREATE your > > own data TYPE to allow more direct use of non-PostgreSQL data types. Here is > > an example that will allow input of any "datetime" data into PostgreSQL : > > > CREATE TYPE datetime AS (datetime timestamptz); > > I think what you probably really want is > > CREATE DOMAIN datetime AS timestamptz; > > or more SQL-spec-compliantly > > CREATE DOMAIN datetime AS timestamp with time zone; > > which essentially makes datetime a direct alias for timestamptz. The > CREATE TYPE approach makes a rowtype containing one timestamptz column, > which isn't really going to act the way you want --- for one thing, > none of the predefined functions and operators for type timestamptz > will accept it. With the DOMAIN approach, they will. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
On Tue, Jan 28, 2003 at 23:39:47 -0500, Wei Weng <wweng@kencast.com> wrote: > What about a UNIQUEIDENTIFIER type? You probably want to use serial type as a replacement. Serial is really int with a default obtained using a sequence.
Dear friends, i'm looking for some program that converts clarion database files to sql instructions. Someone can help-me ? thanks in advance. Nasair Jr. da Silva Lajeado - RS - Brasil xx===============================xx || °v° Nasair Junior da Silva || || /(_)\ Linux User: 246054 || || ^ ^ njunior@univates.br || ||CPD - Desenvolvimento || ||Univates - Centro Universitário|| xx===============================xx
Nasair Junior da Silva schrieb: >Dear friends, >i'm looking for some program that converts clarion database files to sql instructions. > >Someone can help-me ? > Try Google with: clarion sql convert My first result is: http://www.clarionmag.com/cmag/v1/v1n4convertingtosql.html HTH Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik