Re: Migrating from mysql. - Mailing list 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-