{"id":3219,"date":"2024-01-10T13:52:33","date_gmt":"2024-01-10T04:52:33","guid":{"rendered":"https:\/\/bdsl.jbnu.ac.kr\/blog\/?p=3219"},"modified":"2024-01-10T13:52:35","modified_gmt":"2024-01-10T04:52:35","slug":"postgresql","status":"publish","type":"post","link":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/","title":{"rendered":"PostgreSQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">PostgreSQL is an open source database management software widely used in scientific researches. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PostgreSQL Homepage: <a href=\"https:\/\/www.postgresql.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.postgresql.org\/<\/a><\/li>\n<\/ul>\n\n\n\t\t\t\t<div class=\"wp-block-uagb-table-of-contents uagb-toc__align-left uagb-toc__columns-1  uagb-block-c01d33a2      \"\n\t\t\t\t\tdata-scroll= \"1\"\n\t\t\t\t\tdata-offset= \"30\"\n\t\t\t\t\tstyle=\"\"\n\t\t\t\t>\n\t\t\t\t<div class=\"uagb-toc__wrap\">\n\t\t\t\t\t\t<div class=\"uagb-toc__title\">\n\t\t\t\t\t\t\tTable Of Contents\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"uagb-toc__list-wrap \">\n\t\t\t\t\t\t<ol class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#1-installation\" class=\"uagb-toc-link__trigger\">1. Installation<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#administrator-account\" class=\"uagb-toc-link__trigger\">Administrator account<\/a><\/li><\/ul><\/li><li class=\"uagb-toc__list\"><a href=\"#2-create-a-user-and-a-database\" class=\"uagb-toc-link__trigger\">2. Create a user and a database<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#21-create-a-user\" class=\"uagb-toc-link__trigger\">2.1. Create a user<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#22-grant-privileges\" class=\"uagb-toc-link__trigger\">2.2. Grant privileges<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#23-administrator-change-authentication-method\" class=\"uagb-toc-link__trigger\">2.3. (Administrator) Change Authentication method<\/a><\/li><\/ul><\/li><\/ul><\/li><li class=\"uagb-toc__list\"><a href=\"#3-create-a-database\" class=\"uagb-toc-link__trigger\">3. Create a database<\/a><li class=\"uagb-toc__list\"><a href=\"#4-use-python-interface\" class=\"uagb-toc-link__trigger\">4. Use Python interface<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#41-installation\" class=\"uagb-toc-link__trigger\">4.1. Installation<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#42-connect-to-the-database\" class=\"uagb-toc-link__trigger\">4.2. Connect to the database<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/li><li class=\"uagb-toc__list\"><a href=\"#5-tutorial\" class=\"uagb-toc-link__trigger\">5. Tutorial<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#51-connect-to-the-database-as-an-administrator\" class=\"uagb-toc-link__trigger\">5.1. Connect to the database as an administrator<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#52-create-a-user-account\" class=\"uagb-toc-link__trigger\">5.2. Create a user account<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#53-create-a-database\" class=\"uagb-toc-link__trigger\">5.3. Create a database<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#54-grant-privileges-to-the-database\" class=\"uagb-toc-link__trigger\">5.4. Grant privileges to the database<\/a><li class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#55-connect-with-test-user\" class=\"uagb-toc-link__trigger\">5.5. Connect with test_user<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#handle-authentication-error\" class=\"uagb-toc-link__trigger\">Handle authentication error<\/a><\/li><\/ul><li class=\"uagb-toc__list\"><a href=\"#56-create-a-table\" class=\"uagb-toc-link__trigger\">5.6. Create a table<\/a><ul class=\"uagb-toc__list\"><li class=\"uagb-toc__list\"><a href=\"#how-to-exisit-from-psql\" class=\"uagb-toc-link__trigger\">How to exisit from psql<\/a><\/li><\/ul><li class=\"uagb-toc__list\"><a href=\"#56-establish-connection-with-python\" class=\"uagb-toc-link__trigger\">5.6. Establish connection with Python<\/a><\/ul><\/ul><\/ul><\/ul><\/ol>\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\n\n\n<h2 class=\"wp-block-heading\">1. Installation<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Installation in Ubuntu<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ sudo apt update\n$ sudo apt install postgresql postgresql-contrib\n$ sudo systemctl start postgresql.service <\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Administrator account<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL installation create a administrator account, <code>postgres<\/code>. <code>sudo<\/code> access is used to manage the database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The administrator account can be accessed by the following commands, which activating <code>postgres<\/code> user accessing the database management via <code>psql<\/code>. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ sudo -i -u postgres\n$ psql<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Once connected, you can see the PostgreSQL command prompt that looks like the screen below. SQL script can be executed with this command line interface.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">postgres=# <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. Create a user and a database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Accessing PostgreSQL as an administrator, you can create user accounts. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2.1. Create a user<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE ROLE new_user WITH LOGIN PASSWORD 'password';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Replace <code>new_user<\/code> and <code>password<\/code> with the intended user name and the password.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2.2. Grant privileges<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In order to allow a user to access a database, privileges should be granted to the database using <code>GRANT<\/code> keyword. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">GRANT CONNECT ON DATABASE new_database TO new_user;\nGRANT USAGE ON SCHEMA public TO new_user;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The first line allows <code>new_user<\/code> to connect to the database <code>new_database<\/code>. The second line allow the user to use the default schema. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2.3. (Administrator) Change Authentication method<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The default authentication method checks whether the database user name is the same to the system&#8217;s user name. In order to let any system user to access the database using the password, authentification method should be changed from <code>peer<\/code> to <code>md5<\/code> in PostgreSQL configuration file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The configuration file (<code>pg_hba.conf<\/code>) can be found in the locations: <code>\/etc\/postgresql\/&lt;version&gt;\/main\/<\/code>, <code>\/var\/lib\/postgresql\/&lt;version&gt;\/data\/<\/code>, or in others. In the file, find the authentication section and change the METHOD from <code>peer<\/code> to <code>md5<\/code>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You need administrator priviledge (<code>sudo<\/code>) to edit the configuration file.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"apacheconf\" class=\"language-apacheconf\"># TYPE  DATABASE        USER            ADDRESS                 METHOD\nlocal   all             all                                     md5\nhost    all             all             127.0.0.1\/32            scram-sha-256\nhost    all             all             ::1\/128                 scram-sha-256<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Restart PostgreSQL to apply the change.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">sudo systemctl reload postgresql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Create a database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In order to grant privilege to a database, the database should exist. Database can be created with the following script.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE DATABASE new_database;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL usage can be found in <a href=\"https:\/\/www.w3schools.com\/postgresql\/index.php\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.w3schools.com\/postgresql\/index.php<\/a>. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">4. Use Python interface<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL database can be accessed via Python interface. <code>psycopg2<\/code> is one of widely used libraries allowing to access the database using SQL statements.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Homepage: <a href=\"https:\/\/www.psycopg.org\/docs\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.psycopg.org\/docs\/<\/a><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">4.1. Installation <\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The library can be installed using <code>pip<\/code>. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">pip install psycopg2<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">4.2. Connect to the database<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">import psycopg2\nconn = psycopg2.connect(\n    dbname=\"new_database\",\n    user=\"new_user\",\n    password=\"password\",\n    host=\"localhost\"\n)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5. Tutorial<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">5.1. Connect to the database as an administrator<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In linux shell, execute the following commands to access PostgreSQL.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ sudo -i -u \n$ psql<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5.2. Create a user account<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE ROLE test_user WITH LOGIN PASSWORD 'test_user';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5.3. Create a database<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE DATABASE test_database;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5.4. Grant privileges to the database<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">GRANT CONNECT ON DATABASE test_database TO test_user;\nGRANT USAGE ON SCHEMA public TO test_user;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The first line allows the access to the database. The second lets the user to use system default schema.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">5.5. Connect with test_user<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">psql -U test_user -W -d test_database<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Type in the password <code>test_user<\/code> to login the database.<\/p>\n\n\n\n<div class=\"wp-block-uagb-inline-notice uagb-inline_notice__align-left uagb-block-f4bd8e39\"><button class=\"uagb-notice-close-button\" type=\"button\" aria-label=\"Close\"><\/button><h4 class=\"uagb-notice-title\">Handle authentication error<\/h4><div class=\"uagb-notice-text\">If you get the error including the following message, change the section (2.3.). <br>FATAL: password authentication failed for user &#8220;test_user&#8221;<\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">5.6. Create a table <\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a table with name <code>test<\/code> and have an attribute <code>id<\/code>. Then, fill in a value to the table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">CREATE TABLE test (id INT);\nINERT INTO test (id) values (10);<\/code><\/pre>\n\n\n\n<div class=\"wp-block-uagb-inline-notice uagb-inline_notice__align-left uagb-block-e69961c9\"><button class=\"uagb-notice-close-button\" type=\"button\" aria-label=\"Close\"><\/button><h4 class=\"uagb-notice-title\">How to exisit from psql<\/h4><div class=\"uagb-notice-text\">To exist from <code>psql<\/code> command mode, type <code>\\q<\/code> and enter.<\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">5.6. Establish connection with Python<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Connect to the database using <code>psycopg2<\/code> library.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">import psycopg2\nconn = psycopg2.connect(\n    dbname=\"test_database\",\n    user=\"test_user\",\n    password=\"test_user\",\n    host=\"localhost\"\n)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Execute SQL command to retrieve value from the database.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">cur = conn.cursor()\ncur.execute(\"SELECT * from test\")\nfor row in cur.fetchall():\n    print(row)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Exeute SQL command to insert a value to the table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">cur.execute(\"INSERT INTO test (id) VALUES (20)\")\nconn.commit()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Execute data retrieval code again to confirm record insertion.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">cur.execute(\"SELECT * from test\")\nfor row in cur.fetchall():\n    print(row)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL is an open source database management software widely used in scientific researches. This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface. 1. Installation Installation in Ubuntu Administrator account PostgreSQL installation create a administrator account, postgres. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":"","_members_access_role":[],"_members_access_error":""},"categories":[23],"tags":[],"class_list":["post-3219","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL - Biomedical Data Science Laboratory<\/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:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL - Biomedical Data Science Laboratory\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL is an open source database management software widely used in scientific researches. This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface. 1. Installation Installation in Ubuntu Administrator account PostgreSQL installation create a administrator account, postgres. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Biomedical Data Science Laboratory\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-10T04:52:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-10T04:52:35+00:00\" \/>\n<meta name=\"author\" content=\"sphong\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"sphong\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/\"},\"author\":{\"name\":\"sphong\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#\\\/schema\\\/person\\\/8d22f775fcc218b1184ec0d890034e9b\"},\"headline\":\"PostgreSQL\",\"datePublished\":\"2024-01-10T04:52:33+00:00\",\"dateModified\":\"2024-01-10T04:52:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/\"},\"wordCount\":503,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/\",\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/\",\"name\":\"PostgreSQL - Biomedical Data Science Laboratory\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#website\"},\"datePublished\":\"2024-01-10T04:52:33+00:00\",\"dateModified\":\"2024-01-10T04:52:35+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/\",\"name\":\"Biomedical Data Science Laboratory\",\"description\":\"Home page for Biomedical Data Science Laboratory.\",\"publisher\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#organization\",\"name\":\"Biomedical Data Science Laboratory\",\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/logo.png\",\"contentUrl\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/logo.png\",\"width\":1792,\"height\":638,\"caption\":\"Biomedical Data Science Laboratory\"},\"image\":{\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/#\\\/schema\\\/person\\\/8d22f775fcc218b1184ec0d890034e9b\",\"name\":\"sphong\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg\",\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg\",\"contentUrl\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg\",\"caption\":\"sphong\"},\"description\":\"Assistant professor Department of Molecular Biology Jeonbuk National University\",\"sameAs\":[\"https:\\\/\\\/bdsl.jbnu.ac.kr\"],\"url\":\"https:\\\/\\\/bdsl.jbnu.ac.kr\\\/blog\\\/author\\\/sphong\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL - Biomedical Data Science Laboratory","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:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL - Biomedical Data Science Laboratory","og_description":"PostgreSQL is an open source database management software widely used in scientific researches. This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface. 1. Installation Installation in Ubuntu Administrator account PostgreSQL installation create a administrator account, postgres. [&hellip;]","og_url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/","og_site_name":"Biomedical Data Science Laboratory","article_published_time":"2024-01-10T04:52:33+00:00","article_modified_time":"2024-01-10T04:52:35+00:00","author":"sphong","twitter_card":"summary_large_image","twitter_misc":{"Written by":"sphong","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/#article","isPartOf":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/"},"author":{"name":"sphong","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#\/schema\/person\/8d22f775fcc218b1184ec0d890034e9b"},"headline":"PostgreSQL","datePublished":"2024-01-10T04:52:33+00:00","dateModified":"2024-01-10T04:52:35+00:00","mainEntityOfPage":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/"},"wordCount":503,"commentCount":0,"publisher":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#organization"},"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/","url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/","name":"PostgreSQL - Biomedical Data Science Laboratory","isPartOf":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#website"},"datePublished":"2024-01-10T04:52:33+00:00","dateModified":"2024-01-10T04:52:35+00:00","breadcrumb":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bdsl.jbnu.ac.kr\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#website","url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/","name":"Biomedical Data Science Laboratory","description":"Home page for Biomedical Data Science Laboratory.","publisher":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bdsl.jbnu.ac.kr\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#organization","name":"Biomedical Data Science Laboratory","url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-content\/uploads\/2022\/11\/logo.png","contentUrl":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-content\/uploads\/2022\/11\/logo.png","width":1792,"height":638,"caption":"Biomedical Data Science Laboratory"},"image":{"@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/#\/schema\/person\/8d22f775fcc218b1184ec0d890034e9b","name":"sphong","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-content\/uploads\/2022\/04\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg","url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-content\/uploads\/2022\/04\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg","contentUrl":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-content\/uploads\/2022\/04\/cropped-\uc99d\uba85\uc0ac\uc9c4-96x96.jpg","caption":"sphong"},"description":"Assistant professor Department of Molecular Biology Jeonbuk National University","sameAs":["https:\/\/bdsl.jbnu.ac.kr"],"url":"https:\/\/bdsl.jbnu.ac.kr\/blog\/author\/sphong\/"}]}},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"sphong","author_link":"https:\/\/bdsl.jbnu.ac.kr\/blog\/author\/sphong\/"},"uagb_comment_info":0,"uagb_excerpt":"PostgreSQL is an open source database management software widely used in scientific researches. This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface. 1. Installation Installation in Ubuntu Administrator account PostgreSQL installation create a administrator account, postgres.&hellip;","_links":{"self":[{"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/posts\/3219","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/comments?post=3219"}],"version-history":[{"count":20,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/posts\/3219\/revisions"}],"predecessor-version":[{"id":3260,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/posts\/3219\/revisions\/3260"}],"wp:attachment":[{"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/media?parent=3219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/categories?post=3219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bdsl.jbnu.ac.kr\/blog\/wp-json\/wp\/v2\/tags?post=3219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}