SELECTīooks Code language: SQL (Structured Query Language) ( sql ) Update existing key-value pair Now, you can check to see if the "freeshipping" => "yes" pair has been added successfully. SET attr = attr || '"freeshipping"=>"yes"' :: hstore Code language: SQL (Structured Query Language) ( sql ) With hstore column, you can easily add a new key-value pair to existing rows e.g., you can add free shipping key to the attr column of the books table as follows: UPDATE books WHERE attr -> 'ISBN-13' = '978-1449370000' Ĭode language: SQL (Structured Query Language) ( sql ) Add key-value pairs to existing rows For example, the following query retrieves the title and weight of a book that has ISBN-13 value matches 978-1449370000: SELECT You can use the -> operator in the WHERE clause to filter the rows whose values of the hstore column match the input value. For example, if we want to know ISBN-13 of all available books in the books table, we can use the -> operator as follows: SELECT attr -> 'ISBN-13' AS isbnīooks Code language: SQL (Structured Query Language) ( sql ) Use value in the WHERE clause Postgresql hstore provides the -> operator to query the value of a specific key from an hstore column. Querying data from an hstore column is similar to querying data from a column with native data type using the SELECT statement as follows: SELECT attr FROMīooks Code language: SQL (Structured Query Language) ( sql ) Query value for a specific key ) Code language: SQL (Structured Query Language) ( sql ) Query data from an hstore column Both keys and values are quoted using double quotes (“”). The data that we insert into the hstore column is a list of comma-separated key =>value pairs. ) Code language: SQL (Structured Query Language) ( sql ) We use the INSERT statement to insert data into the hstore column as follows: INSERT INTO books (title, attr) ) Code language: SQL (Structured Query Language) ( sql ) Insert data into PostgreSQL hstore column We use the CREATE TABLE statement to create the books table as follows: CREATE TABLE books ( The data type of the attr column is hstore. attr stores attributes of the book such as ISBN, weight, and paperback.id is the primary key that identifies the book.We create a table named books that has three columns: CREATE EXTENSION hstore Code language: SQL (Structured Query Language) ( sql ) Create a table with hstore data type Enable PostgreSQL hstore extensionīefore working with the hstore data type, you need to enable the hstore extension which loads the contrib module to your PostgreSQL instance. Notice that keys and values are just text strings only. The hstore data type is very useful in many cases, such as semi-structured data or rows with many attributes that are rarely queried. The hstore module implements the hstore data type for storing key-value pairs in a single value. Summary: in this tutorial, you’ll learn how to work with PostgreSQL hstore data type.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |