Re: migrate from PostgreSQL to Oracle
- From: "Remigiusz Sokolowski" <remigiusz.sokolowski@xxxxxxxxxx>
- To: <jose.soares@xxxxxxxxxxxxxx>
- Date: Wed, 03 Dec 2008 09:41:28 +0100
jo pisze:
> Hi all,
>
> I'm new to Oracle, I'm trying to migrate from PostgreSQL to Oracle.
>
> I have this table in PostgreSQL that I would like to convert to Oracle.
> There are a couple of errors here:
>
> 1) I can't create the CHECK constraints on date_start and date_end,
> because Oracle doesn't allow this syntax.
> Is there an work around for this?
I'm not sure, but in article by Steven Feuerstein (though may be I
remember the author wrongly) there was such workaround - to create
deterministic function, which returns actually sysdate (and this way is
not deterministic) and can be used for this - anyway it is against
Oracle principle to use in constraints a deterministically behaved code
> 2) I would like to have the last_modify column updated, every time
> someone update the row.
> In Pg I had the pgplsql update_last_modify() and the
> reg_on_update_set_last_modify trigger, but I can't transalte them to
> PL/PSQL.
just create a trigger as You do in Postgresql - this is pretty much similar
>
> 3) I would like to create an index on rows having type column in 'A' or
> 'P' but 'C'.
here the best way is to create an index on name + function which returns
A, P and null for C
>
>
> Could you please give me some help?
>
> CREATE TABLE reg (
> code VARCHAR2(10) PRIMARY KEY,
> name VARCHAR2(100),
> type CHAR CHECK (type IN ('A','C','P'),
> date_start DATE CHECK (date_start <= CURRENT_DATE),
> date_end DATE,
> bdn_id NUMBER,
> bdn_cod_reg VARCHAR2(10),
> last_modify TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> CONSTRAINT reg_date_end_check CHECK (date_end BETWEEN date_start AND
> CURRENT_DATE)
> );
>
>
> CREATE FUNCTION update_last_modify() RETURNS trigger
> AS
> $$
> BEGIN
> NEW.last_modify := CURRENT_TIMESTAMP;
> RETURN NEW;
> END;
> $$
> LANGUAGE plpgsql;
>
>
> CREATE TRIGGER reg_on_update_set_last_modify
> BEFORE UPDATE ON reg
> FOR EACH ROW EXECUTE PROCEDURE update_last_modify();
>
> CREATE UNIQUE INDEX uniq_name on reg (name, type) where type = 'A' and
> typo ='P';
>
>
> j
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos : DBA at DUSB
addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone: +48 58 667 17 43
-----------------------------------------------------------------------------------------
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem:
0000021828,
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
o kapitale zakladowym i wplaconym w wysokosci: 227.593.500,00 zlotych,
NIP: 586-000-78-20, REGON: 190024711
-----------------------------------------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
