In MySQL, when you want to see the constitution of a table, there is an SQL statement that can be executed like any other statement:
SHOW CREATE TABLE nombreTabla;
I want to know if there is an equal query for postgresql .
I have seen that there are statements to execute from the command line, but I want to know if there is a proper SQL statement that shows the
CREATE TABLE of a certain table.
The type of output you would like
I would like a type of output in the form of text, which shows the exact script of the creation of the table, just as it does in MySQL, something like this:
CREATE TABLE `liturgia` ( `id_celebracion` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `id_liturgia` int(10) unsigned zerofill NOT NULL, `id_tipo` int(11) NOT NULL, `id_tiempo` int(11) NOT NULL, `dia` int(11) NOT NULL, `semana` int(11) NOT NULL COMMENT 'Mes si es un santo', `estatus` int(11) NOT NULL DEFAULT '0', `id_color` int(1) DEFAULT NULL, PRIMARY KEY (`id_celebracion`), UNIQUE KEY `id_tipo` (`id_tipo`,`id_tiempo`,`dia`,`semana`) ) ENGINE=MyISAM AUTO_INCREMENT=840 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
You can query the
select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'nombre_tabla'
I have not been able to remove the
primary key , although with another select it can be removed.
To see something similar to the script it would be something like this:
SELECT 'CREATE TABLE ' || relname || E'\n(\n' || array_to_string( array_agg( ' ' || column_name || ' ' || type || ' '|| not_null) , E',\n') || E'\n);\n' from ( SELECT c.relname, a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) as type, case when a.attnotnull then 'NOT NULL' else 'NULL' END as not_null FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'company' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum ) as tabledefinition group by relname;
i got it from here