docs/content/stable/explore/ysql-language-features/databases-schemas-tables.md
To manage data in a database efficiently, you need to follow a structured process that involves creating databases, tables, and schemas. The following detailed guide can help you understand and implement each step.
{{<tip>}} For the list of supported and unsupported schema relations operations, see Schema operations. {{</tip>}}
{{% explore-setup-single-new %}}
A database is the highest level of data organization and serves as a container for all objects such as tables, views, indexes, functions, and schemas. A YugabyteDB cluster can manage multiple databases and each database is isolated from the others, ensuring data integrity and security.
By default, a database named yugabyte is already created. To create a new database, testdb, run the following statement:
CREATE DATABASE testdb;
This creates an empty database where you can create tables.
To switch or connect to the new database, use the \c meta-command as follows:
\c testdb
You should see the following output:
You are now connected to database "testdb" as user "yugabyte".
testdb=#
To list all databases, use the \l or \list meta-commands.
testdb=# \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 |
To drop or delete the database, connect to another database and then use the DROP command.
{{<note>}} You cannot drop the database you are connected to. {{</note>}}
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
A table is the fundamental database object that stores the actual data in a structured format, consisting of rows and columns. Tables are created in a specific schema (by default the public schema) and contain the data that applications and users interact with. Each table has a defined structure, with columns representing the different attributes or fields of the data, and rows representing individual records or entries.
Create a table using the CREATE TABLE statement.
CREATE TABLE users (
id serial,
username CHAR(25) NOT NULL,
email TEXT DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE
To list all tables, use the \dt meta-command.
yugabyte=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+----------
public | users | table | yugabyte
To list more information about the tables you created, use the \d+ meta-command.
yugabyte=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------+----------+----------+---------+-------------
public | users | table | yugabyte | 3072 kB |
public | users_id_seq | sequence | yugabyte | 0 bytes |
The users_id_seq sequence is the result of the serial datatype that has been used in the definition of the id column.
After the tables are set up, you can add data to them. To add a record to the table, you can use the INSERT command.
INSERT INTO users VALUES(1, 'Yoda');
As the statement does not have an explicit value for the column email, the default value of NULL is set for that column.
You can retrieve data from tables using the SELECT statement. For example:
SELECT * FROM users;
To retrieve only certain columns, you can specify the column name as follows:
SELECT username FROM users;
After a table is created, you might need to alter it by adding, removing, or modifying columns. You can use the ALTER TABLE command to perform these actions.
To add a new column address, run the following command:
ALTER TABLE users ADD COLUMN address TEXT;
To drop an existing column, say enabled, you can run the following command:
ALTER TABLE users DROP COLUMN enabled ;
To modify the name of a column, say to change the name of the id column to user_id, do the following:
ALTER TABLE users RENAME COLUMN id to user_id;
A schema is a logical container in a database that holds database objects such as tables, views, functions, and indexes. Schemas provide a way to organize objects into logical groups, making it easier to manage large databases with many objects, and avoiding name conflicts. By default, YugabyteDB creates a schema named public in each database.
To create the schema with name myschema, run the following command:
testdb=# CREATE SCHEMA myschema;
List the schemas as follows:
yugabyte=# \dn
Name | Owner
----------+----------
myschema | yugabyte
public | postgres
(2 rows)
To see which schema is currently the default, run the following:
yugabyte=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
To create a table in a specific schema, prefix the table name with the schema name. For example, create the table users in the schema myschema:
yugabyte=# CREATE TABLE myschema.users(
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 public schema is still the selected schema, and running the \d meta-command would not list the table you just created.
To set myschema as the current schema in this session, do the following:
SET search_path=myschema;
yugabyte=# SHOW search_path;
search_path
-------------
myschema
List the table you created.
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
----------+---------+-------+----------
myschema | users | table | yugabyte
To drop the schema myschema and all the objects inside it, first change the current schema.
yugabyte=# SET search_path=public;
Next, run the DROP statement as follows:
yugabyte=# DROP SCHEMA myschema CASCADE;
You should see the following output.
NOTICE: drop cascades to table myschema.users
DROP SCHEMA
Managing users (also called roles) involves creating, altering, and deleting users, and managing their permissions.
By default, YugabyteDB has two admin users already created: yugabyte (the recommended user) and postgres (mainly for backward compatibility with PostgreSQL).
You can display the current user information 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 | {}
You can create a user with the CREATE USER command. For example, to create a user yoda with password feeltheforce as follows.
CREATE USER yoda WITH PASSWORD 'feeltheforce';
You can change passwords of existing users using the ALTER USER command. For example:
ALTER USER yoda WITH PASSWORD 'thereisnotry';
Users can be granted privileges to perform certain operations on databases and tables. Privileges include actions like SELECT, INSERT, UPDATE, DELETE, and more. You can provide privileges to users using the GRANT command:
GRANT SELECT, INSERT ON TABLE users TO yoda;
To give a user superuser privileges (full administrative rights), you can use the ALTER USER command. For example:
ALTER USER yoda WITH SUPERUSER;
To revoke superuser privileges, do the following:
ALTER USER yoda WITH NOSUPERUSER;
To delete a user, use the DROP USER command:
DROP USER yoda;