Cuando se trata de modificar el orden de las columnas de una tabla en PostgreSQL, nos encontramos con que no hay una forma directa de hacerlo, como sí existe en MySQL y te lo muestro aquí. Respecto a esto no existe una estrategia única, por lo que voy a estar mostrando 3 formas diferentes para lograrlo.

Para todo cambio que vayamos a hacer en la estructura de nuestras tablas, es sumamente importante tener un respaldo actualizado de la misma.

Para cambiar el orden de las columnas de una tabla te voy a mostrar las siguientes formas:

1. Reconstruir la tabla con ayuda de una tabla temporal.
2. Alterar la estructura de la tabla con ayuda de columnas temporales.
3. Usar una vista.

Imaginemos que tenemos la siguiente tabla con algunos datos:

CREATE TABLE personas
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

INSERT INTO personas (id, apellido, nombre, apodo) VALUES
(1, 'Stark', 'Tony', 'Ironman'),
(2, 'Romanoff', 'Natasha', 'Viuda Negra'),
(3, 'Parker', 'Peter', 'Spiderman'),
(4, 'Danvers', 'Carol', 'Capitana Marvel'),
(5, 'Rogers', 'Steven', 'Capitán América'),
(6, 'Banner', 'Bruce', 'Hulk');

Se vería de esta manera:

Si queremos alterar el orden de esta tabla y llevar la columna apellido después de la columna nombre, podemos usar estas alternativas:

Opción 1. Reconstruir la tabla con ayuda de una tabla temporal.

Lo primero que hacemos será crear una tabla temporal para copiar nuestros datos:

//Creamos una tabla temporal
CREATE TABLE personas_copia
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

//Copiamos los datos existentes en la tabla personas
INSERT INTO personas_copia
SELECT * FROM personas;

Luego que aseguramos nuestros datos, podemos borrar la tabla que tiene la estructura que queremos cambiar (si tenemos referencias de otra tabla a la tabla que queremos borrar, primero debemos borrar estas referencias y posteriormente volver a crearlas al finalizar el proceso):

//Borramos la tabla original
DROP TABLE personas;

Seguidamente podemos crear la tabla con las columnas en el orden deseado:

//Crear la tabla ordenada
CREATE TABLE personas
(
    id integer,
    nombre varchar(50),
    apellido varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

Volvemos a copiar nuestros datos:

//Copiamos los datos guardados
INSERT INTO personas
SELECT id, nombre, apellido, apodo FROM personas_copia;

Nos quedaría nuestra tabla ordenada:

Ahora ya podemos borrar la tabla temporal:

//Borramos la tabla temporal
DROP TABLE personas_copia;

Opción 2. Alterar la estructura de la tabla con ayuda de columnas temporales.

El primer paso en esta opción será renombrar todas las columnas que se encuentran desde de la posición donde queremos ordenar la columna (en este caso queremos que ahora nombre este donde esta apellido):

//Renombramos la columna apellido
ALTER TABLE personas
RENAME COLUMN apellido TO apellido_ant;

//Renombramos la columna nombre
ALTER TABLE personas
RENAME COLUMN nombre TO nombre_ant;

//Renombramos la columna apodo
ALTER TABLE personas
RENAME COLUMN apodo TO apodo_ant;

Ahora vamos a crear las columnas en el orden deseado:

//Creamos primero la columna nombre
ALTER TABLE personas
ADD nombre character varying;

//Luego creamos la columna apellido
ALTER TABLE personas
ADD apellido character varying;

//Finalmente creamos la columna apodo
ALTER TABLE personas
ADD apodo character varying;

Luego procedemos a copiar los datos de las columnas originales que cambiamos el nombre, a las nuevas columnas que se encuentran en la nueva posición deseada:

//Copiamos los datos
UPDATE personas SET
nombre = nombre_ant,
apellido = apellido_ant,
apodo = apodo_ant;

Nos quedaría hasta el momento de la siguiente manera:

Finalmente nos queda borrar las columnas viejas:

//Borramos la columna
ALTER TABLE personas
DROP COLUMN apellido_ant;

//Borramos la columna
ALTER TABLE personas
DROP COLUMN nombre_ant;

//Borramos la columna
ALTER TABLE personas
DROP COLUMN apodo_ant;

De esta forma nos quedaría nuestra tabla ordenada:

Opción 3. Usar una vista.

Aunque esta alternativa no considera modificar la estructura de la tabla en cuestión, en algunos casos será la solución más fácil para obtener una entidad con el orden requerido de forma rápida y segura:

//Creamos una vista con el orden deseado
CREATE VIEW personas_vista AS
SELECT id, nombre, apellido, apodo
FROM personas;

Cualquiera de las estrategias mostradas es válida para obtener el objetivo, todo va a depender de la capacidad de maniobra que tengamos con nuestras tablas. ¿Conoces alguna otra manera? Déjamela en los comentarios.

Hay momentos en los cuales debemos modificar el orden de una columna de una tabla en nuestra base de datos. Cuando ya tenemos datos guardados quizás no sea una alternativa crearla nuevamente. Afortunadamente MySQL nos brinda una forma sencilla de lograrlo, a diferencia de otras bases de datos como PostgreSQL donde debemos aplicar otras estrategias como las que muestro acá.

Imaginando que tenemos la siguiente estructura en nuestra tabla con algunos datos:

//Creamos la tabla
CREATE TABLE personas
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

//Insertamos algunos datos
INSERT INTO personas (id, apellido, nombre, apodo) VALUES
(1, 'Stark', 'Tony', 'Ironman'),
(2, 'Romanoff', 'Natasha', 'Viuda Negra'),
(3, 'Parker', 'Peter', 'Spiderman'),
(4, 'Danvers', 'Carol', 'Capitana Marvel'),
(5, 'Rogers', 'Steven', 'Capitán América'),
(6, 'Banner', 'Bruce', 'Hulk');

Nos encontraremos con lo siguiente:

Suponiendo que queremos mover la columna apellido, después de la columna nombre, tendremos que ejecutar:

//Ordenamos la columna apellido después de la columna nombre
ALTER table personas
MODIFY COLUMN apellido varchar(50)
AFTER nombre;

Y nos quedaría de esta manera:

De esta forma podemos alterar el orden de las columnas que nos haga falta en una tabla en nuestra base de datos en MySQL.

Existe una manera muy sencilla de generar un diccionario de datos de una base de datos en MySQL a través de simples consultas. Ésto gracias a la información que nos da su INFORMATION SCHEMA.

Al consultar el INFORMATION_SCHEMA de MySQL (que no es más que una base de datos pero que almacena la estructura de nuestras bases de datos) nos encontraremos toda la información referente tablas, vistas y campos contenidos dentro de las mismas.

Las siguientes tablas son las que usaremos para generar nuestro diccionario de datos:

• TABLES.
• COLUMNS.
• TABLE_CONSTRAINTS.
• KEY_COLUMN_USAGE.

Obtener las tablas y vistas de una base de datos en MySQL

Para obtener las tablas y vistas contenidas dentro de una base de datos en MySQL, vamos a acceder a la tabla INFORMATION_SCHEMA.TABLES y listaremos los campos TABLE_NAME (nombre de la tabla) y TABLE_COMMENT (comentario de la tabla), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), todo ello ordenado por TABLE_NAME (nombre de la tabla):

SELECT 
    t1.TABLE_NAME AS tabla_nombre,
    t1.TABLE_COMMENT AS tabla_descripcion
FROM 
    INFORMATION_SCHEMA.TABLES AS t1
WHERE 
    t1.TABLE_SCHEMA='[NOMBRE_BASE_DATOS]'
ORDER BY
    t1.TABLE_NAME;

Obtener las columnas de una tabla o vista de una base de datos en MySQL

Para obtener las columnas de una tabla en MySQL, vamos a acceder a la tabla INFORMATION_SCHEMA.COLUMNS y listaremos los campos COLUMN_NAME (nombre de la columna), COLUMN_DEFAULT (valor por defecto de la columna), IS_NULLABLE (si la columna acepta o no valores nulos), DATA_TYPE (tipo de dato de la columna), NUMERIC_PRECISION ó CHARACTER_MAXIMUM_LENGTH (precisión del tipo de dato de la columna) y COLUMN_COMMENT (comentario de la columna), esta vez filtrando por el campo TABLE_SCHEMA (nombre de base de datos) y TABLE_NAME (nombre de la tabla), todo ello ordenado por ORDINAL_POSITION (orden de nuestra columna en la tabla):

SELECT
    t1.COLUMN_NAME AS columna_nombre,
    t1.COLUMN_DEFAULT AS columna_defecto,
    t1.IS_NULLABLE AS columna_nulo,
    t1.DATA_TYPE AS columna_tipo_dato,
    IFNULL(t1.NUMERIC_PRECISION,
    t1.CHARACTER_MAXIMUM_LENGTH) AS columna_longitud,
    t1.COLUMN_COMMENT AS columna_descripcion
FROM 
    INFORMATION_SCHEMA.COLUMNS t1
WHERE 
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]'
ORDER BY
    t1.ORDINAL_POSITION;

Obtener las restricciones de las columnas de una tabla de una base de datos en MySQL

Para obtener restricciones primarias y foráneas de las tablas de una base de datos en MySQL, vamos a acceder a las tablas INFORMATION_SCHEMA.TABLE_CONSTRAINTS y INFORMATION_SCHEMA.KEY_COLUMN_USAGE, y listaremos los campos CONSTRAINT_TYPE (tipo de restricción de la columna), COLUMN_NAME (nombre de la columna), REFERENCED_TABLE_NAME (nombre de tabla referenciada) y REFERENCED_COLUMN_NAME (nombre de columna de tabla referenciada), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), TABLE_NAME (nombre de la tabla) Y COLUMN_NAME (nombre de la columna), todo ello ordenado por CONSTRAINT_TYPE (tipo de restricción):

SELECT
    t1.CONSTRAINT_TYPE AS tipo_restriccion,
    t2.COLUMN_NAME columna_nombre,
    t2.REFERENCED_TABLE_NAME tabla_referencia,
    t2.REFERENCED_COLUMN_NAME columna_referencia
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    ON t1.CONSTRAINT_CATALOG = t2.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t2.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE
    t1.TABLE_SCHEMA = '[NOMBRE_BASE_DATOS]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]' AND
    t2.COLUMN_NAME = '[NOMBRE_COLUMNA]'
ORDER BY
    t1.CONSTRAINT_TYPE DESC;

Pueden explorar muchas más posibilidades creando nuevas consultas sobre la base de datos INFORMATION_SCHEMA. Si ésta entrada les ha sido de utilidad también les muestro una forma de generar un diagrama entidad relación de manera automatizada.

También pueden ver en mi blog cómo generar un diccionario de datos de una base de datos en PostgreSQL.

PostgreSQL proporciona de manera sencilla toda la información de los objetos que se encuentran en una base de datos a través de su INFORMATION SCHEMA.

Al consultar el INFORMATION_SCHEMA de PostgreSQL (que no es más que una serie de vistas que nos muestran la estructura de nuestras bases de datos) nos encontraremos toda la información referente tablas, vistas y campos contenidos dentro de las mismas.

Las siguientes tablas son las que usaremos para generar nuestro diccionario de datos:

• PG_CLASS.
• TABLES.
• COLUMNS.
• TABLE_CONSTRAINTS.
• KEY_COLUMN_USAGE.
• REFERENTIAL_CONSTRAINTS.
• CONSTRAINT_COLUMN_USAGE.

Obtener las tablas y vistas de una base de datos en PostgreSQL

Para obtener las tablas y vistas contenidas dentro de una base de datos en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.TABLES y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos TABLE_NAME (nombre de la tabla) y la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla), filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos), todo ello ordenado por TABLE_NAME (nombre de la tabla):

SELECT 
    t1.TABLE_NAME AS tabla_nombre,
    PG_CATALOG.OBJ_DESCRIPTION(t2.OID, 'pg_class') AS tabla_descripcion
FROM 
    INFORMATION_SCHEMA.TABLES t1
    INNER JOIN PG_CLASS t2 ON (t2.relname = t1.table_name)
WHERE 
    t1.TABLE_SCHEMA='[NOMBRE_ESQUEMA]'
ORDER BY
    t1.TABLE_NAME;

Obtener las columnas de una tabla o vista de una base de datos en PostgreSQL

Para obtener las columnas de una tabla en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.COLUMNS y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos COLUMN_NAME (nombre de la columna), COLUMN_DEFAULT (valor por defecto de la columna), IS_NULLABLE (si la columna acepta o no valores nulos), DATA_TYPE (tipo de dato de la columna), NUMERIC_PRECISION ó CHARACTER_MAXIMUM_LENGTH (precisión del tipo de dato de la columna), la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla) y DOMAIN_NAME (nombre del dominio de la columna), esta vez filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos) y TABLE_NAME (nombre de la tabla), todo ello ordenado por ORDINAL_POSITION (orden de nuestra columna en la tabla):

SELECT
    t1.COLUMN_NAME AS columna_nombre,
    t1.COLUMN_DEFAULT AS columna_defecto,
    t1.IS_NULLABLE AS columna_nulo,
    t1.DATA_TYPE AS columna_tipo_dato,
    COALESCE(t1.NUMERIC_PRECISION,
    t1.CHARACTER_MAXIMUM_LENGTH) AS columna_longitud,
    PG_CATALOG.COL_DESCRIPTION(t2.OID,
    t1.DTD_IDENTIFIER::int) AS columna_descripcion,
    t1.DOMAIN_NAME AS columna_dominio
FROM 
    INFORMATION_SCHEMA.COLUMNS t1
    INNER JOIN PG_CLASS t2 ON (t2.RELNAME = t1.TABLE_NAME)
WHERE 
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]'
ORDER BY
    t1.ORDINAL_POSITION;

Obtener las restricciones de las columnas de una tabla de una base de datos en PostgreSQL

Para obtener restricciones primarias y foráneas de las tablas de una base de datos en PostgreSQL, vamos a acceder a las tablas INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS y INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, y listaremos los campos CONSTRAINT_TYPE (tipo de restricción de la columna), COLUMN_NAME (nombre de la columna), CONSTRAINT_NAME (nombre de la restricción de la tabla y nombre de la restricción de la tabla referenciada), TABLE_NAME (nombre de tabla referenciada) y COLUMN_NAME (nombre de columna de tabla referenciada), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), TABLE_NAME (nombre de la tabla) y COLUMN_NAME (nombre de la columna), todo ello ordenado por CONSTRAINT_TYPE (tipo de restricción):

SELECT
    t1.CONSTRAINT_TYPE AS tipo_restriccion,
    t2.COLUMN_NAME AS columna_nombre,
    t1.CONSTRAINT_NAME AS restriccion_nombre,
    t4.CONSTRAINT_NAME AS restriccion_referencia_nombre,
    t4.TABLE_NAME AS tabla_referencia,
    t4.COLUMN_NAME AS columna_referencia
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    ON t1.CONSTRAINT_CATALOG = t2.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t2.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t3
    ON t1.CONSTRAINT_CATALOG = t3.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t4
    ON t3.UNIQUE_CONSTRAINT_CATALOG = t4.CONSTRAINT_CATALOG
    AND t3.UNIQUE_CONSTRAINT_SCHEMA = t4.CONSTRAINT_SCHEMA
    AND t3.UNIQUE_CONSTRAINT_NAME = t4.CONSTRAINT_NAME
WHERE
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]' AND
    t2.COLUMN_NAME = '[NOMBRE_COLUMNA]'
ORDER BY
    t1.CONSTRAINT_TYPE DESC;

Pueden explorar muchas más posibilidades creando nuevas consultas sobre la base de datos INFORMATION_SCHEMA. Si ésta entrada les ha sido de utilidad también les muestro una forma de generar un diagrama entidad relación de manera automatizada.

También pueden ver en mi blog cómo generar un diccionario de datos de una base de datos en MySQL.