Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?

pg_dump:

pg_dump -st tablename dbname

or use PostgreSQL GUI Tools(pgAdmin,phpPgAdmin,etc.)

You can try to trace in the PostgreSQL log file what pg_dump --table table --schema-only really does. Then you can use the same method to write your own sql function.

In command line (psql) you can run: \d <table name> to list all columns, their types and indexes.

Building on the first part of @CubicalSoft's answer you can drop in the following function which should work for simple tables (assumes the default 'public' schema' and omits constraints, indexes and user defined data types etc. etc.). @RJS answer is the only way to do it properly at the moment; this is something that should be built into psql!

CREATE OR REPLACE FUNCTION show_create_table(table_name text, join_char text = E'\n' ) 
  RETURNS text AS 
$BODY$
SELECT 'CREATE TABLE ' || $1 || ' (' || $2 || '' || 
    string_agg(column_list.column_expr, ', ' || $2 || '') || 
    '' || $2 || ');'
FROM (
  SELECT '    ' || column_name || ' ' || data_type || 
       coalesce('(' || character_maximum_length || ')', '') || 
       case when is_nullable = 'YES' then '' else ' NOT NULL' end as column_expr
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = $1
  ORDER BY ordinal_position) column_list;
$BODY$
  LANGUAGE SQL STABLE;

I realize I'm a bit late to this party, but this was the first result to my Google Search so I figured I'd answer with what I came up with.

You can get pretty far toward a solution with this query to get the columns:

SELECT *
FROM information_schema.columns
WHERE table_schema = 'YOURSCHEMA' AND table_name = 'YOURTABLE'
ORDER BY ordinal_position;

And then this query for most common indexes:

SELECT c.oid, c.relname, a.attname, a.attnum, i.indisprimary, i.indisunique
FROM pg_index AS i, pg_class AS c, pg_attribute AS a
WHERE i.indexrelid = c.oid AND i.indexrelid = a.attrelid AND i.indrelid = 'YOURSCHEMA.YOURTABLE'::regclass
ORDER BY" => "c.oid, a.attnum

Then it is a matter of building out the query string(s) in the right format.

Postgres extension ddlx (https://github.com/lacanoid/pgddl) does exactly this and more.

DBeaver is one of the best tools for SQL database management. You can get the table query like create table table_name in a very simple way in the DBeaver tool.

• Right-click on the table name.
• Then, Click on Generate SQL and then DDL DDLimage

In pgAdmin 4, just find the table in the tree on the left, e.g.:

Servers
+ PostgreSQL 11
  + Databases
    + MYDATABASENAME
      + Schemas
        + public
          + Tables
            + MYTABLENAME  <-- click this tree element

When the table is selected, open the SQL tab on the right. It displays the CREATE TABLE for the selected table.

As answered in https://serverfault.com/a/875414/333439, with the \d <table> meta-command in psql is possible to show the table structure in database. If you want to view the query used in meta-command, you can use the command psql -E. As described in the manpage, the -E switch echoes the \d meta-commands queries. So, you can launch psql -E, you can view the table structure with \d <table> meta-command, and, according to -E switch, you can view the query generated for describe the table structure

Better

pg_dump -U <user> -h <host> -st <tablename> <db name>