Learn PostgreSQL
Video source: freeCodeCamp Imp points: To get help in psql: \? To generate dummy for tables, visit mockaroo Priveleges in postgresql: here
Posts
Tablespace in postgres
Tablespace in postgres video PostgreSQL : What is a Tablespace in PostgreSQL Demo for tablespaces in PostgreSQL Create a new instance or initialize existing instance mkdir -p /postgres/data/instance3 pg_ctl -D /postgres/data/instance3 initdb or pg_ctl -D data initdb ls -lF data pg_ctl -D data start or By default, when you create objects (like tables, indexes) which need on-disk storage, the Postgres server creates the required files in $PGDATA. By default the Postgres server creates the required files in the default tablespace called pg_default. the location of which is the data directory $PGDATA. Location to place the physical files for postgresql objects. The Default Tablespaces Two tablespaces are automatically created when the database cluster is initialized. pg_global tablespace is used for shared system catalogs. pg_default tablespace is the default tablespace of the template1 and template0 databases The Default Tablespaces location. The location...
users and Roles in postgres
Users and roles in postgres: Users, groups, and roles user groups and roles are synonyms. Create user = create role+ login permission To create a PostgreSQL user postgres=# create user appuser1 with password 'password'; or postgres=# create role appuser_role2 with login password 'password'; Note :All new users and roles inherit permissions from the public role. WHAT IS PUBLIC SCHEMA AND PUBLIC ROLE When a new database is created, PostgreSQL by default creates a schema named public. All new users and roles are by default granted this public role, and can create object in public schema. SEARCH PATH The search path is a list of schema names that PostgreSQL checks when you don’t use a qualified name of the database object. For example, when you select from a table named testtable, PostgreSQL looks for this table in the schemas listed in the search path. postgres=# show search_path; search_path ----------------- "$user", public ...
Schemas in postgres
Schemas in postgres: Video What is Schema in postgresql A PostgreSQL database cluster contains one or more named databases. A database contains one or more named schemas. Schema contains tables, data types, functions, and operators etc. 2/many schemas in one database schema1 : Create a table actor; schema2 : Create a table actor; User can access actor which is present in schema1 and schema2 with same name. Schemas are analogous to directories at the operating system level. How to create a schema CREATE SCHEMA schema1; To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot: database.schema.table select * from actor;. CREATE TABLE schema1.actor ( actor_id integer NOT NULL DEFAULT nextval('actor_actor_id_seq'::regclass), first_name character varying(45) COLLATE pg_catalog."default" NOT NULL, last_name character varying(45) COLLATE pg_catalog."default" NOT NULL, ...
