Re: converting microsoft sql server 2000 sql-code for postgresql - Mailing list pgsql-sql

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)
> 
>