Reputation
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
3 29 58
Newest
 Copy Editor
Impact
~1.0m people reached

Aug
17
comment Upsert - Should I verify the row exists first, attempt an update, or try to insert first?
Neither 1 nor 2. Just add a condition to your where clause in your update so that it only updates something if the column has not that value, e.g. update ... where (...your existing where conditions...) and certain_unique_column <> certain_val_in_event
Aug
15
comment Postgres data integration
You can either use BDR 2ndquadrant.com/en/resources/bdr or use Postgres-XL to distribute your data over multiple nodes
Aug
14
comment postgresql join - too long when no results found
Please read the information here: dba.stackexchange.com/tags/postgresql-performance/info then edit your question and provide the missing information
Aug
11
comment PSQL not executing SQL command - Powershell
Another option you might try: put the SQL statement(s) into a SQL script and then call psql with the -f parameter instead of passing a command directly.
Aug
11
comment PSQL not executing SQL command - Powershell
@530529 If you can run a plain select as in this answer you should also be able to run the copy command. but you should get rid of all the extra (invalid) characters in the command strings.
Aug
11
comment Database Schema for Blank Database is large. 20MB
If you don't have the space to store thousands of empty databases, how can you store them if they do contain data?
Aug
11
comment query that does functionality of listagg without use of it
@Lennart: you are right. The standard requires the use of the recursive keyword, if the CTE is a recursive one. But neither Oracle nor SQL Server accept the keyword - even for recursive CTEs. Only Postgres and Firebird comply with the standard here.
Aug
11
comment query that does functionality of listagg without use of it
You still haven't told us why you want to avoid the listagg() function. "I want an ANSI solution" doesn't really explain it. Do you need this to be portable across different DBMS? The e.g. the one DBMS that does not support some kind of string aggregation, will not be able to use an ANSI solution because it uses a non-standard string concatenation operator (+ instead of ||). So again: why do you want to avoid listagg()?
Aug
11
comment PSQL not executing SQL command - Powershell
I don't really know PowerShell, but this: {'SELECT * FROM myTable';} looks suspicious: You should not embed another ; inside a single statement (and copy to only needs a ; at the very end). And I'm not sure what the {..} is supposed to do - it is not valid SQL.
Aug
10
comment Which DBMS are more standard compliant?
@Vérace: don't forget DB2
Aug
10
comment Which DBMS are more standard compliant?
Neither show tables nor top 10 or limit 10 are ANSI SQL. And with MySQL and SQL Server you actually picked those two that care the least about SQL standards (just mentioning || vs. + for string concatenation)
Aug
9
comment PostgreSQL: How to get a sum of values and the total rows that went into each value?
You want count(*) not count(id)
Aug
8
comment Can Postgres index regular expressions stored in a column?
According to the execution plan, the query only takes 750ms. So it's less than a second - that's far away from the 3 seconds you mention
Aug
7
comment Postgres 8.4 VACUUM FULL performance
Any reason you are running such an outdated (and unmaintained) version? There were tons of improvements to (auto)vacuum in the current release.
Aug
7
comment Postgres query timing divergence
If you are typing them manually, or read them from a file why do you care about a difference of 10 milliseconds?
Aug
6
comment Columns names are case sensitive on postgres
postgresql.org/docs/current/static/…
Aug
6
comment Continuously arriving data with fast filtering on date in PostgreSQL
Using one table per day is essentially doing partitioning: postgresql.org/docs/current/static/ddl-partitioning.html and then you wouldn't need UNIONs and Postgres could eliminate the partitions you don't need.
Aug
6
comment Update a unique column with an unknown number of values
Please post the create table statements for your tables and some sample data and what you want to do with that sample data (edit your question, don't post additional information as comments)
Aug
6
comment Can I use LongText instead of varchar
Which DBMS are you using?
Aug
5
comment Postgres query timing divergence
The value shown by \timing includes the network roundtrips to send the result to the client.