Migración de BD de Mysql a Pg

[cacr]

Saludos

Estoy pasando una BD de mysql, de un módulo de un sistema que quiero probar, a Postgresql...

Lo que tiene el original en Mysql

Código:

-- MySQL dump 10.11
--
-- Host: localhost    Database: p4a_base_application
-- ------------------------------------------------------
-- Server version   5.0.45-Debian_1ubuntu3.3-log

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` text,
  `label` text,
  `position` int(11) default NULL,
  `visible` tinyint(4) default '1',
  `access_level` text,
  `action` text,
  `param1` text,
  `parent_name` text,
  `parent_position` int(11) default NULL,
  `menu` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

LOCK TABLES `menu` WRITE;
INSERT INTO `menu` VALUES (1,NULL,'admin','Admin',1,1,'10',NULL,NULL,NULL,NULL,'Admin'),(2,1,'p4a_users','Users',1,1,'10','openMask',NULL,'admin',1,'Admin->Users'),(3,1,'p4a_menu_mask','Menu',1,1,'10','openMask',NULL,'admin',1,'Admin->Menu');
UNLOCK TABLES;

DROP TABLE IF EXISTS `p4a_menu_id_seq`;
CREATE TABLE `p4a_menu_id_seq` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

LOCK TABLES `p4a_menu_id_seq` WRITE;;
INSERT INTO `p4a_menu_id_seq` VALUES (3);
UNLOCK TABLES;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `user` text,
  `pass` text,
  `level` int(11) default NULL,
  `default_mask` text,
  `name` text,
  `surname` text,
  `country` text,
  `address` text,
  `city` text,
  `tel1` text,
  `tel2` text,
  `fax` text,
  `mobile` text,
  `email` text,
  `note` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',10,'p4a_menu_mask',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
UNLOCK TABLES;

DROP TABLE IF EXISTS `users_id_seq`;
CREATE TABLE `users_id_seq` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

LOCK TABLES `users_id_seq` WRITE;
INSERT INTO `users_id_seq` VALUES (1);
UNLOCK TABLES;

Como lo llevo en Postgresql:

Código:

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

CREATE SCHEMA base_application;

ALTER SCHEMA base_application OWNER TO carbonara;

COMMENT ON SCHEMA base_application IS 'Aplicación sencilla que incluye: autenticación y gestión de users, y gestión de opciones de menú';


SET search_path = base_application, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE menu (
    id serial NOT NULL,
    parent_id integer,
    name character varying(20) NOT NULL,
    label character varying(50),
    position smallint,
    visible boolean DEFAULT true NOT NULL,
    access_level smallint,
    action character varying(100) DEFAULT 'openMask'::character varying,
    param1 text,
    parent_name character varying(20),
    parent_postion smallint,
    menu character varying
);

ALTER TABLE base_application.menu OWNER TO carbonara;

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu', 'id'), 1, false);

CREATE SEQUENCE p4a_menu_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE base_application.p4a_menu_id_seq OWNER TO carbonara;

SELECT pg_catalog.setval('p4a_menu_id_seq', 1, false);

CREATE TABLE users (
    id serial NOT NULL,
    "user" character varying(20) NOT NULL,
    pass character varying NOT NULL,
    level smallint,
    default_mask character varying(50),
    name character varying(100)
);

ALTER TABLE base_application.users OWNER TO carbonara;

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('users', 'id'), 1, false);

INSERT INTO menu VALUES (1, NULL, 'admin', 'Admin', 1, true, 10, NULL, NULL, NULL, NULL, 'Admin');
INSERT INTO menu VALUES (2, 1, 'p4a_users', 'Users', 1, true, 10, 'openMask', NULL, 'admin', 1, 'Admin->Users');
INSERT INTO menu VALUES (3, 1, 'p4a_menu_mask', 'Menu', 1, true, 10, 'openMask', NULL, 'admin', 1, 'Admin->Menu');

INSERT INTO users VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 10, 'p4a_menu_mask', 'Administrador');

ALTER TABLE ONLY menu
    ADD CONSTRAINT menu_pkey PRIMARY KEY (id);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

Levanto la BD en pg, y bien, sin problemas... pero, hago la consulta:

Código:

SELECT * FROM users WHERE user = 'admin' AND pass = '21232f297a57a5a743894a0e4a801fc3'

y no devuelve nada... la cambio a:

Código:

SELECT * FROM users WHERE pass = '21232f297a57a5a743894a0e4a801fc3'

y funciona perfecto! En la tabla, como pueden ver en el dump, sólo existe un registro, y en user está admin

Ven algo que yo no? Alguna idea? Gracias de antemano