Ezequiel Tolnay
  • Member for 7 years, 1 month
  • Last seen this week
How to check if a postgresql backup is finished or not?
3 votes

You can use the function pg_is_in_backup() in PostgreSQL >= 9.3, see Backup Control Functions For example: SELECT pg_is_in_backup(); If the result is false, then the computer is not currently ...

View answer
Within a trigger is there a way to tell if an update or insert came from a trigger or not?
Accepted answer
2 votes

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is ...

View answer
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
2 votes

For checking of initials, I often use casting to "char" (with the double quotes). It's not portable, but very fast. Internally, it simply detoasts the text and returns the first character, and "char" ...

View answer
Migrating master databases between clusters by detaching/attaching them
2 votes

SQLServer can't work without a master database at all as it stores essential configuration information in it (such as the table of databases in the system), so it wouldn't allow you to detach it. ...

View answer
Is it possible to set PostgreSQL roles with privileges this way?
2 votes

What is possible is what's specified in the documentation for GRANT (http://www.postgresql.org/docs/current/static/sql-grant.html). To make modifications on tables, a user must own the object. When a ...

View answer
How to format a phone number in a SELECT statement?
2 votes

Different areas have differing number of digits in their prefix (wikipedia's list): COALESCE(CONCAT('+', CASE WHEN LEFT(phone_number, 1) IN ('1', '7') THEN INSERT(INSERT(INSERT(...

View answer
What is the easiest way to backup PostgreSQL and send backups to Google Drive?
2 votes

I use gdrive (follow the installation and set-up guidelines from the link). Once gdrive is installed, I dump my own databases to a /bak folder daily, and then upload my files to Google Drive's PGBAK ...

View answer
Optimize query to find top N users who commented on a post
2 votes

Your query doesn't loop 10483 times, it loops once (loop=1) for each loop, but it has to sort 10483 rows first prior to discarding all but the first 5 rows. Your last query was almost correct, except ...

View answer
Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)
Accepted answer
2 votes

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could ...

View answer
Postgres Update Limit
2 votes

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10. Edit, using an array in ...

View answer
Best database system to store and compare date ranges
2 votes

PostgreSQL has the datatypes daterange, tsrange (timestamp range) and tstzrange (time stamp with time zone range), along with a number of operators and functions to process, cast and extract ...

View answer
How to select redundant rows
Accepted answer
2 votes

There are many ways for doing this. When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast: SELECT * FROM data d LEFT JOIN ...

View answer
Designing table for document annotation with future columns
Accepted answer
2 votes

JSON documents are ideal for this purpose. MySQL can store JSON documents via its JSON datatype, and it has a variety of function for extracting information in such documents. However, MySQL doesn't ...

View answer
Get data from previous years dynamically
2 votes

You can do that with a simple join and intervals. Assuming the table with your data is named mydata and the table you mention in your question with a list of dates named mydates, you could issue the ...

View answer
Surprising results for data types with type modifier
2 votes

This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters ...

View answer
Can I avoid a join for my select procedures?
Accepted answer
2 votes

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(...

View answer
Need help improving sql query performance
2 votes

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (...

View answer
Query wont compile/run
2 votes

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the ...

View answer
Postgres: set client encoding default
Accepted answer
2 votes

You can also set it as a default for any user or role (takes effect after a new connection is established): ALTER ROLE <rolename> SET client_encoding = 'UTF8';

View answer
How can I remove 'Using temporary ' and speed up my query
Accepted answer
2 votes

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You ...

View answer
PostgreSQL 9.3: Aggregate collection of JSON arrays
Accepted answer
2 votes

You can use json_each and LEFT JOIN LATERAL for this purpose: WITH inventory AS ( SELECT '{ "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15], "bar": [...

View answer
Is it a good approach to duplicate data in a database?
Accepted answer
2 votes

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views. Meanwhile the duplicated data is ...

View answer
Is it possible to create a function for transactions that use more than one database?
Accepted answer
2 votes

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. ...

View answer
Very tricky: Can't update a sequential list SQL-92 ( Filemaker)
2 votes

If you're able to create a table, and update allows you to use the result of a subselect (all valid SQL92 code), you should be able do perform the following multi-step process: CREATE TABLE sorting (...

View answer
enforcing data integrity for sequential relations
Accepted answer
2 votes

You can make use of an EXCLUSION constraint and tsrange datatype instead of two timestamps. create table trip_segments ( segment_id serial primary key, travel_ts tstzrange, -- departure time to ...

View answer
Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?
2 votes

You'll have your reasons for going this route, where it seems you're duplicating information (since id_Products is implied from id_Variants). However, unless what you want to achieve is having ...

View answer
How to optimize this SQL Server query?
2 votes

First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the ...

View answer
Looking for a simpler alternative to a recursive query
2 votes

This was hard! I don't know if this is simpler, but at least it doesn't use window function nor produce rows that require being filtered out. with recursive r(k, n) as ( with t(k) as (values (1),(...

View answer
Updating a table with a not-joined query
2 votes

You can either run the following as many times as it takes to get zero rows updated: UPDATE IncentiveAssignments SET PersonID = (SELECT MIN(PersonID) FROM UnassignedPeople up WHERE NOT EXISTS (...

View answer
Structure for storing table data, lists, text, pictures within PostgreSQL JSON field
Accepted answer
2 votes

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays: CREATE TYPE mydoc AS (content_type text, ...

View answer