I have my pg_trgm module installed. The schema set is extensions. To use it I have to run something like this select: I’m trying to run a statement using the % operator and got the following message. What is necessary to run % or <-> operators? Answer Most probably the problem is with the search_p…
Tag: postgresql
Trigger for checking a given value before INSERT or UPDATE on PostgreSQL
I have to create a trigger to update a database where there are products. Products have an expiration date and before inserting or updating a product I must check if the expirationDate is superior to the current timestamp. If it is I must do the insert/update regularly (this is what I have problems with). If …
Query for array elements inside JSON type
I’m trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { “objects”: [ {“src”:”foo.png”}, {“…
Grant privileges on future tables in PostgreSQL?
I am running PostgreSQL 9.3.1. I have test database and backup user which is used to backup the database. I have no problems with granting privileges to all current tables, but I have to grant privileges each time the new table is added to schema. Is it possible to grant access to tables which will be created…
Real, mathematical “modulo” operation in Postgres?
In Ruby: In Postgres: I can easily write one myself, but I’m curious whether Postgres has a real modulus operation, as opposed to remainder after division. Answer will return 22 instead of -2
How to merge all integer arrays from all records into single array in postgres
I have a column which is of type integer array. How can I merge all of them into a single integer array? For example: If I execute query: I get result set as: How can I get {1,2,3,4,5} as final result? Answer You could use unnest to open up the arrays and then array_agg to put them back together:
Import sql file in node.js and execute against PostgreSQL
I’m looking for an efficient way to take a raw sql file and have it executed synchronously against a postgres database, akin to if you ran it through psql. I have an sql file which creates all databases, imports data, etc. I need to execute this using node.js but cannot find any module which does this a…
Emulating materialized views in PostgreSQL with concurrent refreshes
I’m using PostgreSQL 9.2.4 and would like to emulate a materialized view. Are there any well-known methods for doing this, including concurrent refreshes? Answer PostgreSQL wiki – materialized views links to two trigger-based implementations. The general idea is to put AFTER INSERT OR UPDATE OR DE…
Cannot create a table due to naming conflict
I am trying to create a table in my database and it gives me the following error. Then I thought that the table must exist so I ran the following query: But could not find anything. Then I tried: Any idea how to sort this? i am tying to rename the type by and getting the error. Answer Postgres creates
EXPLAIN ANALYZE within PL/pgSQL gives error: “query has no destination for result data”
I am trying to understand the query plan for a select statement within a PL/pgSQL function, but I keep getting errors. My question: how do I get the query plan? Following is a simple case that reproduces the problem. The table in question is named test_table. The function is as follows: When I run I get the e…