CODEWITHSUNDEEP
sqlitepostgresql
locoboy
locoboy

Reputation: 38960

.schema for postgres

I'm migrating a database from sqlite3 to postgres and am wondering if there are any short tutorials that can teach me the new syntax.

Also, as a short term question, how do I see the schema of a postgres table which is equivalent to .schema in sqlite?

Upvotes: 2

Views: 1301

Answers (4)

DiAngelo
DiAngelo

Reputation: 220

Maybe you can use a PostgreSQL Cheat Sheet:

http://www.postgresonline.com/special_feature.php?sf_name=postgresql83_cheatsheet&outputformat=html

Upvotes: 1

gsiems
gsiems

Reputation: 3790

If you are using psql (and \d... ) then you can

\set ECHO_HIDDEN

to see the sql for the queries that psql is executing to put together the \d... output-- this is useful not only as sql syntax examples but it also shows you where find, and how to connect, the database metadata.

To get the schema name for a table you can:

SELECT  n.nspname AS schema_name,
        c.relname AS table_name
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '<table_name>'
;

(don't know how that compares to .schema)

Upvotes: 1

mu is too short
mu is too short

Reputation: 434965

You could use \d from within psql:

=> \?
...

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
...

=> \d people
                                           Table "public.people"
         Column         |            Type             |                      Modifiers                      
------------------------+-----------------------------+-----------------------------------------------------
 id                     | integer                     | not null default nextval('people_id_seq'::regclass)
 created_at             | timestamp without time zone | not null
 updated_at             | timestamp without time zone | not null
...
Indexes:
    "people_pkey" PRIMARY KEY, btree (id)
...
Check constraints:
    "chk_people_latlng" CHECK ((lat IS NULL) = (lng IS NULL))
....

You can also root around in the information_schema if you're not inside psql.

Upvotes: 3

dmirkitanov
dmirkitanov

Reputation: 1396

You could use pg_dump command line utility, i.e.:

pg_dump --table <table_name> --schema-only <database_name>

Depending on your environment you probably need to specify connection options (-h, -p, -U switches).

Upvotes: 5

Related Questions