{"id":200,"date":"2017-11-29T09:07:00","date_gmt":"2017-11-29T09:07:00","guid":{"rendered":"https:\/\/blinksql.com\/?p=200"},"modified":"2017-11-29T09:07:00","modified_gmt":"2017-11-29T09:07:00","slug":"postgresql-basics","status":"publish","type":"post","link":"https:\/\/blinksql.com\/?p=200","title":{"rendered":"Postgresql Basics"},"content":{"rendered":"

Create Database:
\n<\/strong>-> A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database.
\n-> The full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).
\nSyntax:\/usr\/local\/pgsql\/bin\/createdb sandbox_db -p 5435
\n\"create<\/p>\n

Connect to Database:<\/strong>
\ncmd:\/usr\/local\/pgsql\/bin\/psql sandbox_db -p 5435
\n<\/strong>
\n\"connect<\/p>\n

To list all the available databes:<\/strong>
\ncmd:select datname from pg_database;
\n<\/strong>
\n\"list<\/p>\n

<\/strong>
\nCreate Schema:
\n<\/strong>-> Schemas are a purely logical structure and who can access what is managed by the privilege system.
\ncmd:create schema dev_schema;<\/strong>
\n\"create<\/p>\n

Set Search Path:<\/strong>
\n-> It is used to group the tables in the particular schema and while looking for the object first search in the schema if the object is not available then error is reported.
\n-> By default all the tables are under public schema.
\n-> To see the current search path use the cmd.
\ncmd:show search_path;
\n-> To set the deatch path
\ncmd:set search_path=’dev_schema’;
\n\"search<\/p>\n

Drop Database:<\/strong>
\n-> If the databse is not needed then we can remove the database to free the space.
\ncmd:drop database test_db;
\n\"drop<\/p>\n

Tablespace:<\/strong>
\n-> It allows database administrators to define locations in the file system where the files representing database objects can be stored.
\nUSES:<\/strong>
\n1. If the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.
\n2. To optimize performance by the following
\n-> An index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device.
\n-> At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.
\nSyntax:CREATE TABLESPACE fastspace LOCATION ‘\/DB\/postgresql\/data’;
\n<\/strong>-> To see the available tablespaces
\ncmd:select spcname from pg_tablespace;
\n<\/strong>
\n-> To set the default table space
\ncmd:SET default_tablespace = space1;<\/strong>
\n<\/strong><\/p>\n

-> To create the table in specified tablespace
\ncmd:CREATE TABLE test(no varchar,name varchar) TABLESPACE fastspace;
\n<\/strong>
\n\"table<\/p>\n","protected":false},"excerpt":{"rendered":"

Create Database: -> A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database. -> The…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":""},"categories":[1],"tags":[],"yoast_head":"\nPostgresql Basics - Blink SQL<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blinksql.com\/?p=200\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Postgresql Basics - Blink SQL\" \/>\n<meta property=\"og:description\" content=\"Create Database: -> A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database. -> The…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blinksql.com\/?p=200\" \/>\n<meta property=\"og:site_name\" content=\"Blink SQL\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-29T09:07:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blinksql.com\/wp-content\/uploads\/2017\/11\/create-database.png\" \/>\n<meta name=\"author\" content=\"Hannie\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@harika_sanaka\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hannie\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blinksql.com\/?p=200#article\",\"isPartOf\":{\"@id\":\"https:\/\/blinksql.com\/?p=200\"},\"author\":{\"name\":\"Hannie\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4\"},\"headline\":\"Postgresql Basics\",\"datePublished\":\"2017-11-29T09:07:00+00:00\",\"dateModified\":\"2017-11-29T09:07:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blinksql.com\/?p=200\"},\"wordCount\":386,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blinksql.com\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blinksql.com\/?p=200#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blinksql.com\/?p=200\",\"url\":\"https:\/\/blinksql.com\/?p=200\",\"name\":\"Postgresql Basics - Blink SQL\",\"isPartOf\":{\"@id\":\"https:\/\/blinksql.com\/#website\"},\"datePublished\":\"2017-11-29T09:07:00+00:00\",\"dateModified\":\"2017-11-29T09:07:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blinksql.com\/?p=200#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blinksql.com\/?p=200\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blinksql.com\/?p=200#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/blinksql.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Postgresql Basics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blinksql.com\/#website\",\"url\":\"https:\/\/blinksql.com\/\",\"name\":\"Blink SQL\",\"description\":\" Thinking SQL\",\"publisher\":{\"@id\":\"https:\/\/blinksql.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blinksql.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blinksql.com\/#organization\",\"name\":\"Blink SQL\",\"url\":\"https:\/\/blinksql.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png\",\"contentUrl\":\"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png\",\"width\":280,\"height\":172,\"caption\":\"Blink SQL\"},\"image\":{\"@id\":\"https:\/\/blinksql.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4\",\"name\":\"Hannie\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blinksql.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g\",\"caption\":\"Hannie\"},\"sameAs\":[\"http:\/\/blinksql.com\",\"https:\/\/twitter.com\/harika_sanaka\"],\"url\":\"https:\/\/blinksql.com\/?author=2\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Postgresql Basics - Blink SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blinksql.com\/?p=200","og_locale":"en_US","og_type":"article","og_title":"Postgresql Basics - Blink SQL","og_description":"Create Database: -> A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database. -> The…","og_url":"https:\/\/blinksql.com\/?p=200","og_site_name":"Blink SQL","article_published_time":"2017-11-29T09:07:00+00:00","og_image":[{"url":"https:\/\/blinksql.com\/wp-content\/uploads\/2017\/11\/create-database.png"}],"author":"Hannie","twitter_card":"summary_large_image","twitter_creator":"@harika_sanaka","twitter_misc":{"Written by":"Hannie","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blinksql.com\/?p=200#article","isPartOf":{"@id":"https:\/\/blinksql.com\/?p=200"},"author":{"name":"Hannie","@id":"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4"},"headline":"Postgresql Basics","datePublished":"2017-11-29T09:07:00+00:00","dateModified":"2017-11-29T09:07:00+00:00","mainEntityOfPage":{"@id":"https:\/\/blinksql.com\/?p=200"},"wordCount":386,"commentCount":0,"publisher":{"@id":"https:\/\/blinksql.com\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blinksql.com\/?p=200#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blinksql.com\/?p=200","url":"https:\/\/blinksql.com\/?p=200","name":"Postgresql Basics - Blink SQL","isPartOf":{"@id":"https:\/\/blinksql.com\/#website"},"datePublished":"2017-11-29T09:07:00+00:00","dateModified":"2017-11-29T09:07:00+00:00","breadcrumb":{"@id":"https:\/\/blinksql.com\/?p=200#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blinksql.com\/?p=200"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blinksql.com\/?p=200#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blinksql.com\/"},{"@type":"ListItem","position":2,"name":"Postgresql Basics"}]},{"@type":"WebSite","@id":"https:\/\/blinksql.com\/#website","url":"https:\/\/blinksql.com\/","name":"Blink SQL","description":" Thinking SQL","publisher":{"@id":"https:\/\/blinksql.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blinksql.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/blinksql.com\/#organization","name":"Blink SQL","url":"https:\/\/blinksql.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blinksql.com\/#\/schema\/logo\/image\/","url":"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png","contentUrl":"https:\/\/blinksql.com\/wp-content\/uploads\/2022\/11\/cropped-BlinkSQL-removebg.png","width":280,"height":172,"caption":"Blink SQL"},"image":{"@id":"https:\/\/blinksql.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/blinksql.com\/#\/schema\/person\/f04b99304f59e14956e768e764d2afd4","name":"Hannie","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blinksql.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c5876b3497d2c2eb98256b7c183220c1?s=96&d=mm&r=g","caption":"Hannie"},"sameAs":["http:\/\/blinksql.com","https:\/\/twitter.com\/harika_sanaka"],"url":"https:\/\/blinksql.com\/?author=2"}]}},"_links":{"self":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts\/200"}],"collection":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=200"}],"version-history":[{"count":0,"href":"https:\/\/blinksql.com\/index.php?rest_route=\/wp\/v2\/posts\/200\/revisions"}],"wp:attachment":[{"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blinksql.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}