Schemas and tables

This section covers basic topics including how to connect to your cluster using the YSQL shell, and use the shell to manage databases, schemas, and tables.

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

YSQL shell

Use the ysqlsh shell to interact with a Yugabyte database cluster using the YSQL API. Because ysqlsh is derived from the PostgreSQL shell psql code base, all psql commands work as is in ysqlsh. Some default settings such as the database default port and the output format of some of the schema commands have been modified for YugabyteDB.

Using ysqlsh, you can:

  • interactively enter SQL queries and see the query results
  • input from a file or the command line
  • use meta-commands for scripting and administration

ysqlsh is installed with YugabyteDB and is located in the bin directory of the YugabyteDB home directory.

Connect to a node

From the YugabyteDB home directory, connect to any node of the database cluster as shown below:

$ ./bin/ysqlsh -h 127.0.0.1

This should bring up the following prompt, which prints the version of ysqlsh being used.

ysqlsh (11.2-YB-2.5.1.0-b0)
Type "help" for help.

yugabyte=#

You can check the version of the database server by running the following query:

yugabyte=# SELECT version();

The output shows the YugabyteDB server version, and is a fork of PostgreSQL v11.2:

                                              version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2-YB-2.5.1.0-b0 on x86_64-<os, compiler version, etc>, 64-bit
(1 row)

Query timing

You can turn the display of how long each SQL statement takes (in milliseconds) on and off by using the \timing meta-command, as follows:

yugabyte=# \timing
Timing is on.

Users

By default, YugabyteDB has two admin users already created: yugabyte (the recommended user) and postgres (mainly for backward compatibility with PostgreSQL). You can check this as follows:

yugabyte=# \conninfo

This should output the following:

You are connected to database "yugabyte" as user "yugabyte" on host "127.0.0.1" at port "5433".

To check all the users provisioned, run the following meta-command:

yugabyte=# \du
                                     List of roles
  Role name   |                         Attributes                         | Member of 
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yb_db_admin  | No inheritance, Cannot login                               | {}
 yb_extension | Cannot login                                               | {}
 yb_fdw       | Cannot login                                               | {}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Databases

YSQL supports databases and schemas, much like PostgreSQL.

To create a new database testdb, run the following statement:

CREATE DATABASE testdb;

To list all databases, use the \l meta-command.

yugabyte=# \l
                                   List of databases
      Name       |  Owner   | Encoding | Collate |    Ctype    |   Access privileges
-----------------+----------+----------+---------+-------------+-----------------------
 postgres        | postgres | UTF8     | C       | en_US.UTF-8 |
 system_platform | postgres | UTF8     | C       | en_US.UTF-8 |
 template0       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 testdb          | yugabyte | UTF8     | C       | en_US.UTF-8 |
 yugabyte        | postgres | UTF8     | C       | en_US.UTF-8 |
(6 rows)

To connect to this database, use the \c meta-command.

yugabyte=# \c testdb

You should see the following output:

You are now connected to database "testdb" as user "yugabyte".
testdb=#

To drop the database we just created, connect to another database and then use the DROP command.

Connect to another database as follows:

testdb=# \c yugabyte
You are now connected to database "yugabyte" as user "yugabyte".

Use the DROP command as follows:

yugabyte=# DROP DATABASE testdb;
DROP DATABASE

Verify the database is no longer present as follows:

yugabyte=# \l
                                   List of databases
      Name       |  Owner   | Encoding | Collate |    Ctype    |   Access privileges
-----------------+----------+----------+---------+-------------+-----------------------
 postgres        | postgres | UTF8     | C       | en_US.UTF-8 |
 system_platform | postgres | UTF8     | C       | en_US.UTF-8 |
 template0       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | en_US.UTF-8 | =c/postgres          +
                 |          |          |         |             | postgres=CTc/postgres
 yugabyte        | postgres | UTF8     | C       | en_US.UTF-8 |
(5 rows)

Tables

Create a table using the CREATE TABLE statement.

CREATE TABLE users (
  id serial,
  username CHAR(25) NOT NULL,
  enabled boolean DEFAULT TRUE,
  PRIMARY KEY (id)
  );
CREATE TABLE

To list all tables, use the \dt meta-command.

yugabyte=# \dt
yugabyte=# \dt
                List of relations
 Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
 public | users               | table | yugabyte

To list the table and the sequence you created, use the \d meta-command.

yugabyte=# \d
 Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------
 public | users               | table    | yugabyte
 public | users_id_seq        | sequence | yugabyte

To describe the table you created, enter the following:

\d users
yugabyte=# \d users
                                Table "public.users"
  Column  |     Type      | Collation | Nullable |              Default
----------+---------------+-----------+----------+-----------------------------------
 id       | integer       |           | not null | nextval('users_id_seq'::regclass)
 username | character(25) |           | not null |
 enabled  | boolean       |           |          | true
Indexes:
    "users_pkey" PRIMARY KEY, lsm (id HASH)

Schemas

A schema is a named collection of tables, views, indexes, sequences, data types, operators, and functions.

To create the schema with name myschema, run the following command:

testdb=# CREATE SCHEMA myschema;
CREATE SCHEMA

List the schemas as follows:

yugabyte=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 myschema | yugabyte
 public   | postgres
(2 rows)

To create a table in this schema, run the following:

yugabyte=# CREATE TABLE myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

At this point, the default schema is still the selected schema, and running the \d meta-command would not list the table you just created.

To see which schema is currently the default, run the following.

yugabyte=# SHOW search_path;

You should see the following output.

   search_path
-----------------
 "$user", public
(1 row)

To set myschema as the default schema in this session, do the following.

SET search_path=myschema;

Now list the table you created.

yugabyte=# SHOW search_path;
 search_path
-------------
 myschema
(1 row)

List the table you created.

yugabyte=# \d
           List of relations
  Schema  |  Name   | Type  |  Owner
----------+---------+-------+----------
 myschema | company | table | yugabyte
(1 row)

To drop the schema myschema and all the objects inside it, first change the current default schema.

yugabyte=# SET search_path=default;

Next, run the DROP statement as follows:

yugabyte=# DROP SCHEMA myschema CASCADE;

You should see the following output.

NOTICE:  drop cascades to table myschema.company
DROP SCHEMA

Quit ysqlsh

To quit the shell, enter the following meta-command:

yugabyte=# \q