Thread: Migrating from mysql.
Hi there,
I'm migrating from MySQL to PostgreSQL and I was wondering whether someone
could help me match some datatypes.
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?
Secondly MySQL supports an 'enum' datatype which allowed a number of
labels to be defined as valid datatypes for a column, i.e:
create table account (
status enum('cancelled','active','onhold') DEFAULT 'active' NOT NULL
...
)
I can't seem to find the equivalent in PostgreSQL. Is there a way of doing
this?
And last but not least I'm used to using the 'desc tablename' sql command
to show the structure of a table within MySQL. How do I do the same in
PostgreSQL.
Many thanks in advance,
Joe
> Hi there, > > I'm migrating from MySQL to PostgreSQL and I was wondering whether someone > could help me match some datatypes. I'm also migrating, and would appreciate any thoughts on how to do it. Spesifically, if there could be any problems with functions not supported on the PostgreSQL and such. Pointers to documents, HOWTOs etc. are much appreciated. -Morten
On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: > And last but not least I'm used to using the 'desc tablename' sql command > to show the structure of a table within MySQL. How do I do the same in > PostgreSQL. In psql "\i tablename". Check out \? or the documentation for all the different backslash commands. You might want \z for access permissions as well. Regards, Giles
On Thu, May 25, 2000 at 04:58:48AM +1000, Giles Lean wrote: > > On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: > > > And last but not least I'm used to using the 'desc tablename' sql command > > to show the structure of a table within MySQL. How do I do the same in > > PostgreSQL. > > In psql "\i tablename". Check out \? or the documentation for all the > different backslash commands. You might want \z for access > permissions as well. Actually, it's "\d tablename". The rest is right, though. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
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...
> 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?
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.
> Secondly MySQL supports an 'enum' datatype which allowed a number of
> labels to be defined as valid datatypes for a column, i.e:
>
> I can't seem to find the equivalent in PostgreSQL. Is there a way of doing
> this?
>
Yes. Use a CHECK constraint. I included one in the following example.
CREATE SEQUENCE mucho_mas_id_seq;
CREATE TABLE mucho_mas (
name TEXT CHECK( name IN ('Larry','Billy')),
worth NUMERIC(14,2)
NOT NULL
DEFAULT '0',
updated TIMESTAMP
DEFAULT CURRENT_TIMESTAMP,
id INTEGER
DEFAULT NEXTVAL('mucho_mas_id_seq')
PRIMARY KEY
);
INSERT INTO mucho_mas (name, worth)
VALUES ('Larry','40000000000.00');
INSERT INTO mucho_mas (name, worth)
VALUES ('Billy','40000000000.01');
-- no mucho mas for me.
--
INSERT INTO mucho_mas (name, worth)
VALUES ('Ron','2.03');
SELECT *
FROM mucho_mas;
-- we need to updates on a view, rather than on table itself, because
-- if we update the table directly, we will have a circular rule
-- combination
--
CREATE VIEW mucho_mas_view AS
SELECT * FROM mucho_mas;
CREATE RULE mucho_mas_view_update AS
ON UPDATE TO mucho_mas_view
DO INSTEAD
UPDATE mucho_mas
SET worth = new.worth, updated = CURRENT_TIMESTAMP
WHERE id = old.id;
-- in real life, you'd probably do something more sophisticated to
-- select proper id value, but that's another problem.
--
UPDATE mucho_mas_view
SET worth = '40000000000.02'
WHERE id = 1;
-- Hmm, must have been an accounting mistake. Let's fix that.
--
UPDATE mucho_mas_view
SET worth = '40000000000.03'
WHERE id = 2;
SELECT *
FROM mucho_mas;
DROP VIEW mucho_mas_view;
DROP TABLE mucho_mas;
DROP SEQUENCE mucho_mas_id_seq;
> And last but not least I'm used to using the 'desc tablename' sql command
> to show the structure of a table within MySQL. How do I do the same in
> PostgreSQL.
I noticed someone already responded to this, so I won't repeat.
Good luck! I hope I haven't led you too far astray!
-Ron-
On Wed, 24 May 2000 14:26:32 -0500 "Ross J. Reedstrom" wrote: > Actually, it's "\d tablename". The rest is right, though. Teach me to try to tidy things up before posting won't it? Thanks! Giles (sigh, time for coffee)
On Wed, 24 May 2000, Morten W. Petersen wrote: > > Hi there, > > > > I'm migrating from MySQL to PostgreSQL and I was wondering whether someone > > could help me match some datatypes. > > I'm also migrating, and would appreciate any thoughts on how to do > it. Spesifically, if there could be any problems with functions not > supported on the PostgreSQL and such. Quick and extremely, and I mean, extremely, ugly code attached. It helped me convert some database dumps from mysql to pgsql, however, I do not even recommend fixing it much, its too damn ugly, and written as a hack that grew out of control. (It converts mysql database dump into something that pgsql can load. Or something that you can manually fix up and then load). -- Alex Pilosov | http://www.acecape.com/dsl CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area 325 W 38 St. Suite 1005 | (Stealth Marketing Works! :) New York, NY 10018 |
Attachment
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 #
Jan Wieck wrote: > > Ron Peterson wrote: > > As long as there's someone watching you ;-) :-) > > 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. I'm trying to learn more about triggers. In doing so, I found some example Server Programming Interface (SPI) functions in /contrib. Most germane to this discussion is the 'moddatetime' example, which was provided by Terry Mackintosh, with help from PostgreSQL's very own Jan Wieck... Must be like deja vu all over again. -Ron-