PostgreSQL

PostgreSQL, often simply Postgres, is an object-relational database management system with an emphasis on extensibility and standards compliance.

This post is the collection of most commonly used PostgreSQL commands.

Login to PostgreSQL

psql -Upostgres

Where postgres is PostgreSQL username. It will ask password of postgres user. Once you enter password it will enter to PostgreSQL shell.

List all databases

postgres=# \l

Switch to specific database

postgres=# \c mydatabase;

Here mydatabase is the database name on which you want to switch.

PostgreSQL Schema

A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

List all schema

postgres=# \dn;

Create new schema

postgres=# CREATE SCHEMA myschema;

Here myschema is new schema that I want to create to specific database.

Check specific schema information

postgres=# \dt myschema.*

Where myschema is Schema name.

Check public schema information

postgres=# \dt;

To show the current search path you can use the following command:

postgres=# SHOW search_path;

Switch Schema, And to put the new schema in the path, you could use:

postgres=# SET search_path TO myschema;

Find all records from specific table with a particular schema.

postgres=# select * from public.images;

Here public is public schema and images is table name.

postgres=# select * from myschema.images;

Here myschema is schema name of database and images is table within myschema schema.