7

It seems to be the default behaviour of SQL Workbench, have a look at this article: How do I change the resolution or scale of decimal data type on SQL Workbench. Quoted from the article: Normally, SQL Workbench doesn't display the decimal data with the full scale. By default the scale is 2. We can change the scale by the setting. Solution ...


4

The only way that I know this is possible is with a deferred constraint. You will need to drop the primary key, alter table x drop constraint x_pkey; and add it again as deferrable: alter table x add primary key (id) deferrable initially immediate; Before performing the update you can defer the constraint: set constraints x_pkey deferred; and then ...


4

You could use a deferred constraint. For that you need to drop and re-create the primary key: CREATE UNIQUE INDEX mytable_primkey ON mytable (id); ALTER TABLE mytable DROP CONSTRAINT mytable_pkey; ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey DEFERRABLE INITIALLY DEFERRED; The update itself could then be done like that: UPDATE mytable ...


4

I would simply separate the query logic and the insert logic: with vals (a,b) as ( VALUES (1, null), (null, 2), (2, 3) ), new_rows as ( insert into all_sums (sum) select a + b from vals where a + b is not null ) select a + b from vals; Online example


3

This will be fixed in version 12, which will be released soon. I think the summary here is that we are only willing to do so much work to try to prove a partial index can be used, because all queries have to go through that work even if they don't end up using the partial index. In this change, they just found a more efficient way to do that work in this ...


3

I have solved thanks to Jeff Janes on the pgsql-performance mailing list: The GIN index was not used by PostgreSQL for the "NOT" operation. Creating a Btree index on the whole array solved the problem, allowing an index only scan. Now the query takes only a few milliseconds instead of minutes.


3

Index scans fetch from the heap for every single row. That is what makes it not be an index-only scan. It only makes sense to display the count for index-only scans, as that is the only case in which it is informative. The line "Buffers:" line may be more informative in general (for a realistic case where you have more than one row at stake). But for ...


2

How fast do you expect this to be? It is taking about the amount of time I would expect (a little slower, but that is probably your hardware). The query may be simple to specify, but it handles a large amount of data. Most of the time of the nested loop is taken up by waiting for its first child (the hash join) to returns its results. The nested loop ...


2

As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values. Given the next example: CREATE TABLE t (id int, bar int); INSERT INTO t VALUES (1, 7), (2, 8), (3, 30), (4, 25), (5, 24), (6, 24), (7, 35), (8, 40); You can get the previous bar value ordering by id, (if you don't set an order, what is the ...


2

That would mean that at least one of hll_cardinality and hll_union_agg must not be parallel safe. Try \df+ hll_cardinality \df+ hll_union_agg in psql and see what it says under Parallel. If the functions are parallel safe, you can use ALTER TABLE to mark them as such. If in doubt, ask the author of the functions. In addition, the aggregate function must ...


2

You don't show the column definition, but it must be similar to "conversionFactor" decimal(10,2) At any rate, the scale of the column must be 2, so PostgreSQL has to round the number to be able to sore it in that column. You will have to use ALTER TABLE to change the colum definition to be able to store more fractional digits in the column.


2

You can set old_snapshot_threshold and then wait out that amount of time before retrying the VACUUM. (Note that this is a last resort. First resort would be just to identify the long running connections, and making them go away if they are not really needed, or downgrading their isolation level).


2

Your explain plan is a bit confusing, as it looks like the index scan is getting the data for all 200 user_ids at once, but then doing that 200 times. But doing the experiment, that is not what it is doing, each iteration of the nested loop is getting the data for one user_id from that list, not the whole list. So it is just a presentation issue in the ...


2

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types. You could define your own tuple type and use that instead of a record: CREATE TYPE tuple AS (a int, b int); SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*; SELECT 1, (CASE ...


2

You can CREATE EXTENSION pageinspect; Then you can use the function SELECT * FROM bt_page_stats('indexname' , 42); to get some information about block 42 of the index: the number of live and dead entries, the amount of free space and the pointers to the left and right sibling (btpo_prev and btpo_next) and the type (root, intermediate or leaf).


2

The output of pg_upgrade includes this: ... Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh ... We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)


2

PostgreSQL does not use threads, it uses coordinated single-thread processes. It will not use more than max_parallel_workers, system-wide, at one time. If it would exceed that, the queries will not error out, they simply run with fewer worker processes than they "wanted". If the number of connections you have trying to run queries at the same time is ...


2

Is the original b-tree totally redundant at this point? I expected it might still be picked by the planner if only those two columns were used if the b-tree was faster for those data types but that seems like it is not the case. Not totally. The btree index can be used for ordering (although for 3 distinct values in each column, its not clear how much call ...


2

No, a database can have only a single name. You will have to disconnect all sessions before you can rename the database. You will have to update the database name in all connection strings. The latter can be avoided if you use LDAP connection string lookup, but I guess you don't want to run an LDAP server in a small organization.


2

psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum


2

If you don't have any foreign keys referencing that table (i.e. "incoming" foreign keys), you could do something like this: -- save the squashed data into a temporary table create temp table new_data as SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value from ledger GROUP BY date_trunc('hour', date), user_id; -- get ...


2

If first and second can be any value and could be embedded in other words without any kind of delimiter (eg, foofirstbar), then yes, using trigrams is probably as good as you're going to get. If there are a limited number of values for first and second, you could create an expression index of the name column passed through a regexp_replace to add spaces ...


2

Autovacuum will start right away, but it takes some time to process the table. During that time, you don't see a decrease in the number of dead tuples. To make autovacuum be more aggressive, don't make it start sooner, but make it faster. A threshold of 10000 with a scale factor of 0 seems insane to me, it will cause autovacuum to permanently process this ...


2

Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference. The reason is dump being a logical one it will only create statements to insert your data and the dead rows would anyways be invisible to it. If you have lots of updates and deletes happening or in other words your db ...


2

Simply re-insert deleted records: WITH cte AS ( DELETE FROM parent WHERE {conditions} RETURNING * ) INSERT INTO parent SELECT * FROM cte; fiddle


1

SELECT a.id, a.market_id FROM all_products a INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID WHERE EXISTS (SELECT 1 FROM all_products a INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID ) UNION ALL SELECT a.id, a.market_id FROM all_products a WHERE NOT EXISTS (SELECT 1 FROM all_products ...


1

Turns out this was much easier to fix than I though. Being completely new to both SQL and postgres I foolishly assumed that psql had something to do with the problem. As it turns out the directory /media/user in which Storage is mounted did not have Read & Write permissions set for other users, so even though I would have been allowed to access the file ...


1

There are many possible query styles, most will readily use your PK index on (sensor_id, time) as it fits the task. (Postgres can read indexes backwards practically as fast.) This should be near perfect: SELECT s.sensor_id, sd.time, sd.value FROM unnest ('{1,3}'::int[]) s(sensor_id) LEFT JOIN LATERAL ( SELECT * FROM sensor_data sd WHERE sd....


1

Based on your comments, the INSERT could look something like this: INSERT INTO log (userid, clientaddr, calltime, query) SELECT user_id, -- function argument a.client_addr, a.query_start, a.query FROM pg_stat_activity AS a WHERE a.pid = pg_backend_pid(); The key to identifying the current session is the process ID of the backend ...


1

To do it with built-in tools of Postgres and the hstore module exclusively, without involving jsonb, as requested, replace: audit_row.row_data = hstore(NEW.*) - excluded_cols; with: audit_row.row_data = hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(hstore(NEW.*) - excluded_cols) WHERE value IS NOT NULL ) ); While ...


Only top voted, non community-wiki answers of a minimum length are eligible