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