Re: Migrating from mysql.

Ron Peterson wrote:
> Joe Karthauser wrote:
> >
> > Hi there,
> >
> > I'm migrating from MySQL to PostgreSQL and I was wondering whether someone
> > could help me match some datatypes.
>
> I'm trying to learn PostgreSQL myself.  They say there's no better way
> to learn than to teach, so here goes...

    As long as there's someone watching you ;-)

> > Firstly MySQL has a 'timestamp' datatype which automatically updates with
> > the current timestamp whenever an instance containing it is inserted or
> > updated.  Is there an equivalent datatype in PostgreSQL?

    AFAIK   that's   a   non-compliant   interpretation   of  the
    "timestamp" data type.  "timestamp" defines a point in  time,
    not  the  last  time  this  datum was created/changed - did I
    misread the specs?

> No.  Try a combination of default value and an update rule.  I've
> included an example below.  There was a discussion on this list recently
> about when to use rules vs. triggers.  You might want to read the
> archives about that.  Something I need to review more myself.

    Exactly that. Rules are good for redirecting an entire  query
    to  another operation (INSERT->UPDATE for example) or to fire
    additional "global" queries on other tables.

    Rules can do things triggers cannot. If you want  to  make  a
    VIEW  to  act correctly on UPDATE or DELETE, you need a rule.
    Anything else can be done with a trigger (safer). The  reason
    for  this  is  that only rules can redirect a scan (what must
    happen if you issue an UPDATE/DELETE to  find  the  requested
    rows).  If  you  have  a  trigger  on UPDATE to a view, it'll
    "never"  be  fired,  because  the  views  rewrite  rule  will
    redirect  the  scan  to  "whatever  the view defined". So the
    views heap is never scanned (yes - I  think  views  shouldn't
    have  a  heap).  At least view heaps should never contain any
    data, (because a scan on them will allways be  redirected  to
    somewhere else, so what would that data be good for?).

    Most  of  the  real world applications I've seen up to now do
    INSERT, UPDATE and DELETE operations on a single, unique  key
    base.  If backed by stupid "data containers", they spend alot
    of (error prone) efford  to  maintain  referential  integrity
    when  updating/deleting  datums.  Real  databases ensure this
    based on the schema. If your  application  is  some  sort  of
    that, don't consider rules as a solution - look for a trigger
    and move the data integrity logic into the database.  If  you
    really    perform   UPDATE/DELETE   operations   that   touch
    hundereds++ of rows at once, gimme the entire  schema  so  we
    can workout the trigger vs. rule quesions in detail.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #