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(at)Yahoo(dot)com #