PostgreSQL Module #59 Flashcards
What type of database is PostgreSQL?
It is a relational database. Think TABLES :-)
What command needs to be entered on the command i line in terminal to get access to the database?
psql postgres (which gives access to the postgres db created by default)
How are new databases added to Postgre ?
By calling the CREATE DATABASE test; command. Don’t forget the semicolon as it is necessary.
What’s the syntax to create a new table in our DB?
CREATE TABLE users {
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL
);
How can you connect to a specific database
By typing
\c test or the name of the db
What’s the syntax for listing your databases (and templates) in PostgreSQL?
\list or \l
How are templates and databases delineated in PostgreSQL?
Databases take the name you assign and templates are called templates which are numbers.
template1
template2 etc
What are templates in PostgreSQL? How are they created?
Templates are predefined databases. You can use them to pre-populate new databases and you use the syntax to generate them:
CREATE DATABASE databasename Template template0
How can we view a more detailed overview of our databases showing the disc sizes for each?
\list+ or +
How are new users created in PostgreSQL?
There are no users, just roles.
How are roles created in PostgreSQL?
On MacOS, you’re automatically assigned a role when you run psql postgres.
How can you view roles?
By typing: \du
How would you create a new role?
Use this syntax:
CREATE ROLE ; a better example is
CREATE ROLE testing;
But this example creates a user without login rights.
How are Roles with Login capabilities created?
CREATE ROLE rolename WITH LOGIN;
How can users login under their ROLE?
Use this syntax:
psql postgres -U testing rolename