20,450 reputation
13064
bio website
location Vienna, Austria
age 94
visits member for 2 years, 11 months
seen 29 mins ago

6h
comment Translating MySQL query to PostgreSQL
You are still correct about the missing GROUP BY items. The three columns come from three different tables. (The alternative with max() would produce completely different results though.)
7h
revised PostgreSQL equivalent of MySQL query variables?
fix format, trim noise
7h
revised Translating MySQL query to PostgreSQL
edited tags
7h
comment Translating MySQL query to PostgreSQL
Postgres implemented the SQL99 version with version 9.1 (mostly). A PRIMARY KEY in the GROUP BY clause covers the whole table in the SELECT list. (Other functional dependencies like UNIQUE constraints are not covered, though.) Details in this related answer: dba.stackexchange.com/questions/41494/…
8h
comment Upsert statement never completes, infinite loop?
Your sample isn't of much use without the table definition to go along with it. Best as CREATE TABLE statement.
8h
revised Upsert statement never completes, infinite loop?
edited tags
8h
answered Upsert statement never completes, infinite loop?
8h
revised Issue with composite type in an UPSERT function
adapt title, trim noise, typos, format
8h
revised Issue with composite type in an UPSERT function
clean up, add links
19h
answered Bulk insert in multiple tables
19h
revised Bulk insert in multiple tables
format, tags
1d
comment Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?
Obviously, you cannot UPDATE´ *parts* of a geometric data type that is NULL` - even if you update all parts. You would have to update the whole value as one to make it non-null. And not sure I understand your last question in the comment.
1d
answered What is the best approach to process huge amount of data insertion efficiently
1d
comment What is the best approach to process huge amount of data insertion efficiently
You posted a fragment of a trigger function, not a trigger. Please post the complete function, including the header and the trigger itself. Plus, relevant table definitions with all indexes and constraints. Ideally original, complete CREATE scripts or at least what you get in psql with \d tbl.
1d
revised Unique constraint to enforce max one null per item
edited tags
1d
comment Unique constraint to enforce max one null per item
I reopened the question, since you have sneaked in a second question (we prefer one question per question). This may be of help for the main question (unique and NULL): Multi-column unique constraint and NULL values. And this for the 2nd one (exclusion constraint): Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
1d
comment Unique constraint to enforce max one null per item
The exclude constraint requires that you install the extension btree_gist for the included integer. Details: dba.stackexchange.com/questions/37351/…
1d
revised Speed up sorting in this query
clarify title, trim noise, minor edits
1d
revised Query to return output column names and data types of a query, table or view
sharpen title, add tag, minor edits
1d
answered Query to return output column names and data types of a query, table or view