A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
4

That looks like an array. If that is the case, you can remove the first element of the array using an UPDATE statement: update the_table scrap_value = scrap_value[2:]; [2:] selects all elements of the array starting with the second. The result of that is then used to override the existing array.


2

A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is Read Committed. This is documented in https://www.postgresql.org/docs/...


2

There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you ...


2

column called "scrap_value" ... contains a set of numbers e.g. {100000,125000,150000,175000,200000} Whilst many DBMSs will support this sort of thing, it breaks basic Database Normalisation rules and, since you say you're just starting out with this sort of thing, let's set you on the Right Road to being with: These values should be split out into a ...


2

The query simply has to read the 98175 rows which are located in almost as many 8KB-blocks, so there is no way around reading all these blocks, and if they are not cached, that is going to be slow. Your only chance to make that faster is to make sure that the rows are lumped together in fewer blocks. That could be done by rewriting the table like this: ...


1

No it does not create a read or write lock on films table as this creates ACCESS SHARE LOCK; to test this open two sessions in session one run this command begin; create table films2 as table films; then in the second session run this command Select * from films; Update films set something = 0 If you need to lock films table you have to ...


1

Never mind, Its possible, I have tested with this. But we have to make sure that if you have any PK, Unique key it should unique across your source databases(all 4).Or on the subscription database create the table without the constraints.


1

If you really want to get the count once the INSERT is finished, you could do something like this. with source_data as ( SELECT pkey, col1, col2 FROM table2 ), input_count as ( select count(*) as source_count from source_data ), new_rows as ( INSERT INTO table1 (pkey, col1, col2) select * from source_data ON CONFLICT DO NOTHING returning ...


1

Insert not needed for to count: WITH cte1 AS ( SELECT COUNT(*) cnt FROM table1 ), cte2 AS ( SELECT COUNT(*) cnt FROM table2 ), cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) ) SELECT cte1.cnt "Records in table1", cte2.cnt "Records in table2", cte3.cnt "Conflicts count", cte2.cnt - cte3.cnt "Potential inserts count" ...


1

I do believe that the diagnosis of Laurenz Albe is correct but, at least for me on pg 11, setting cursor_tuple_fraction = 1.0 did not result in a parallel plan for queries launched from DBeaver. But what did work was setting the ResultSet fetch size in DBeaver to 0. Note that DBeaver will fetch all of the results for every query with this setting so you ...


1

You can use EXCEPT to remove the complementing rows. SELECT friend1,friend2 FROM friends WHERE friend1=2 or friend2=2 EXCEPT SELECT friend2,friend1 FROM friends WHERE friend1=2 or friend2=2; But maybe using a CTE will perform better. WITH buddies AS ( SELECT friend1,friend2 FROM friends WHERE friend1=2 or friend2=2 ) SELECT friend1,...


1

force manually an autovacumm policy on mergency situations This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough". No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first. You can use ...


1

If you have hot_standby_feedback = on, a manual VACUUM won't delete the dead row versions if there is a long running query on the standby. The only way to do that would be to set old_snapshot_threshold to a value different from the default value. Then VACUUM (and autovacuum) will remove dead tuples even if an old transaction might still need them. Note that ...


1

Yes, that is the idea. In the case of a replication conflict PostgreSQL has only two options: cancel the query delay the application of replicated changes. Setting max_standby_streaming_delay to -1 will delay replication indefinitely long. There are ways to reduce replication conflicts: Set hot_standby_feedback = on to remove replication conflicts caused ...


1

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale) SELECT to_char(timestamp, 'Day') FROM fact_table In general the table form is, to me, cleaner (when there is no suitable function) Perhaps with an in-line table if there is a good reason to not have a permanent table. SELECT day_name FROM ...


1

I should have tried a bit more first. Now that I run a few examples, I seems like yes, the above queries can all use an index-only scan. As soon as a column outside of the covering index is referred to, a regular access method is used (e.g. an index scan id a fitting index is in place). It would be nice if the Postgres documentation would make this more ...


1

Procedure are a new thing in Postgresql as of version 11, there are two big differences between Procedures and Functions One Procedures can issue a commit or rollback and keep processing, Functions can not issue a commit or rollback. Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is an ...


1

You could mess with the pg_attribute catalog table and set attislocal to FALSE, but modifying the system catalogs is unsupported and dangerous. In my opinion, that is not warranted for a mere esthetic improvement. The dump works just as fine as it is.


1

That is silly You should instead take one pg_basebackup per day and archive the transaction log with archive_command. That way. you can use point-in-time-recovery to restore the database to any given point in time. See the documentation for more.


1

Your assessment is spot on, and there is no better way of doing it. As an alternative, you could do without the sort, but delete in smaller batches, each in its own transaction. That is still susceptible to deadlock, but the likelihood is smaller, and re-running the transaction doesn't hurt quite as much.


1

here are a couple of ideas. One does the constraint check in a function. the second modifies the table, creates a trigger to add in the missing data and creates a new index on the three fields that have to be checked CREATE TRIGGER check_jsonb BEFORE INSERT ON data FOR EACH ROW EXECUTE PROCEDURE CREATE FUNCTION public._check_jsonb()...


1

If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea: remove the uniqueness constraint when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates have some sort of parallel process going through the table periodically and removing duplicates


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