Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
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', 'LJ', '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 [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
Guy Fraser <[EMAIL PROTECTED]> 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
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
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" <[EMAIL PROTECTED]> To: "Guy Fraser" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, January 28, 2003 8:55 PM Subject: Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql > Guy Fraser <[EMAIL PROTECTED]> 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 > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
On Tue, Jan 28, 2003 at 23:39:47 -0500, Wei Weng <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
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', 'LJ', '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 [EMAIL PROTECTED]) -- 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
--- william windels <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
