Tag: postgresql

Statistics on Query Time (PostgreSQL)

I have a table with a billion rows and I would like to determine the average time and standard deviation of time for several queries of the form: I have a thousand random values for col1 stored in another table. Is there some way to store how long each of these queries took (in milliseconds) in a separate table, so

From Now() to Current_timestamp in Postgresql

datetime postgresql sql

In mysql I am able to do this: now in postgresql I am using this query: but I get this error: How can I resolve ? Answer Use an interval instead of an integer:

Similarity function in Postgres with pg_trgm

fuzzy-search postgresql sql

I’m trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error: If I add explicit casts to text I get the error: My query is: Do I need to do something to initalize pg_trgm? Answer You have to install pg_trgm. In debian, source this sql:

List columns with indexes in PostgreSQL

indexing postgresql sql

I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. Does anything like this exist for PostgreSQL? I’ve tried d at the psql command prompt (with the -E option to show SQL) but it doesn’t show the information I’m looking for.

Duplicate value in a postgresql table

postgresql sql

I’m trying to modify a table inside my PostgreSQL database, but it says there is duplicate! what is the best way to find a duplicate value inside a table? kinda a select query?

What’s “.” in postgresql dump file?

dump postgresql sql

I have a Postgresql dump file which i’m trying to restore. I get this error regarding an invalid data i guess. ERROR: invalid input syntax for integer: “.” and when i checked the file, there are …

SQL LIKE condition to check for integer?

postgresql sql

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter “A”: That’s fine for letters, but how do I list all items starting with any number? For what it’s worth this is on a Postgres

How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)

auditing logging postgresql sql triggers

Using PostgreSQL triggers, is it possible to record the changes that have happened to a table due to INSERT or UPDATE SQL statements and log them to a file for later execution. This is only to be used temporally so just something quick and dirty would do. Answer example of an audit trigger from https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

Can I add a UNIQUE constraint to a PostgreSQL table, after it’s already created?

database indexing postgresql sql unique-constraint

How To Do Percent/Total in SQL?

postgresql sql

I have an typical CUSTOMER/ORDERS set of tables and I want to display the total percentage of sales a particular customer is responsible for. I can get the total number of orders in the system like so: And I can get the the total number of orders made by the customer like so: How can I combine these into a