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-
