How to see the script for creating a table (CREATE TABLE) in Postgresql?

Question:

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 .

I have seen that there are statements to execute from the command line, but I want to know if there is a properly SQL statement that shows the CREATE TABLE of a certain table.

The kind of output I would like

I would like a type of output in the form of text, that shows the exact script of the table creation, 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

Answer:

You can refer to the INFORMATION_SCHEMA.COLUMNS table.

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'nombre_tabla'

Prove sentence

Fountain. SO IN


I have not managed 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 out of here

Scroll to Top