From: | Ron Peterson <rpeterson(at)yellowbank(dot)com> |
---|---|
To: | Joe Karthauser <joe(at)pavilion(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Migrating from mysql. |
Date: | 2000-05-24 19:52:36 |
Message-ID: | 392C3304.2927D1D5@yellowbank.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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-
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 2000-05-24 20:16:36 | Re: Postgres Instability |
Previous Message | Giles Lean | 2000-05-24 19:46:49 | Re: Migrating from mysql. |