Posts

PostgreSQL CREATE INDEX Statement

Image
  Summary : in this tutorial, you will learn how to use the PostgreSQL   CREATE INDEX   statement to define a new index for a table. Introduction to PostgreSQL CREATE INDEX statement An index is a separate data structure that enhances the speed of data retrieval from a table, at the cost of additional writes and storage required to maintain it. An index allows you to improve the query performance when using it appropriately, especially on large tables. To create an index on one or more columns of a table, you use the  CREATE INDEX  statement. Here’s the basic syntax of the  CREATE INDEX  statement: CREATE INDEX [ IF NOT EXISTS ] index_name ON table_name(column1, column2, ...); Code language: SQL (Structured Query Language) ( sql ) In this syntax: First, specify the index name after the  CREATE INDEX  clause. Second, use the  IF NOT EXISTS  option to prevent an error if the index already exists. Third, provide the table name t...
Post a Comment
Read more

Understanding LIKE vs ILIKE in PostgreSQL

  The ILIKE operator is a PostgreSQL extension that works exactly like the LIKE operator, except that it is case insensitive. This means that it does not matter whether the string and the pattern have the same case or not. For example, the pattern 'A%' matches both 'apple' and 'Apple' when using the ILIKE operator.
Post a Comment
Read more

Import and Export data

export : pg_dump -h localhost -p 5432 -U user -d database_name  -f pg_dump.sql import : psql -h localhost -d database_name -U user -p 5432 -f pg_dump.sql Notes : - the geospatial data : need install postgis in postgres (or use postgis/postgis image instead of postgres image in docker) - make sure to the destination server have similar users/roles as the source server
Post a Comment
Read more

How To Install and Use PostgreSQL on Ubuntu 18.04

  Introduction Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information. PostgreSQL , or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks. This guide demonstrates how to install Postgres on an Ubuntu 18.04 VPS instance and also provides instructions for basic database administration. Prerequisites To follow along with this tutorial, you will need one Ubuntu 18.04 server that has been configured by following our  Initial Server Setup for Ubuntu 18.04  guide. After completing this prerequisite tutorial, your server should have a non- root  user with sudo permissions and a basic firewall. Step 1 ...
1 comment
Read more