8

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 ...


6

you can return multiple result sets from a procedure - similar to the way it has always been possible with a function: create procedure getdata(result_one inout refcursor, result_two inout refcursor) as $$ begin open result_one for select * from (values (1,2,3), (4,5,6)) as t(a,b,c); open result_two for select * from (values ('one'),...


6

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 ...


6

The information is stored in pg_depend: SELECT t.oid::regclass AS table_name, a.attname AS column_name, s.relname AS sequence_name FROM pg_class AS t JOIN pg_attribute AS a ON a.attrelid = t.oid JOIN pg_depend AS d ON d.refobjid = t.oid AND d.refobjsubid = a.attnum JOIN pg_class AS s ON s.oid = d.objid WHERE ...


6

Approach 1: At first sight, it seems that I could apply a basic solution because, according to your sample data, each single connection is included in another connection. SELECT e1.node, e1.connections, COALESCE(e2.connections, e1.connections) nodes_in_chain FROM example e1 LEFT JOIN example e2 ON e2.node <> e1.node AND ...


5

Did this feature indeed appear in the Postgres 11 release? No. I think the blog post refers to the "Dynamic result sets from procedures" patch, which did not make it into the released version.


5

documentation means: if you have permanent table TABLE_A and you create a temporary table with the same name TABLE_A, a permanent table will be not visible for your code. But you could request data from the permanent table by add schema to name - schema_name.table_name. Answering to your question - yes, it is safe.


5

Aren't enums already ordered? No. This commit, from 2014, implies that BRIN indexes should work for ENUM types. That's actually not what that commit says. From the commit-message on the link you provided This type of operator class we call "Minmax", and we supply a bunch of them for most data types with B-tree opclasses. Since the BRIN code is ...


5

From this post on /r/PostgreSQL to an answer by Laurenz Albe it seems that Heap Only Tuples (HOT) updates may be responsible. From the description of HOT updates in src/backend/access/heap/README.HOT Effectively, space reclamation happens during tuple retrieval when the page is nearly full (<10% free) and a buffer cleanup lock can be acquired. ...


5

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 ...


5

I'm not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you're using is pretty much all you need (you might need to specify the password with a flag - not sure), unless you do want to do it in two steps with --schema-only and then with --data-only: pg_dump is a utility for backing up a PostgreSQL database. It ...


4

You can unnest json array: Postgres WITH ORDINALITY: When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest(). Have a look at this answer of Erwin ...


4

The optimal DB design always depends on the complete picture. Generally, there is hardly anything faster than a plain btree index for your simple query. Introducing json or jsonb or even a plain array type in combination with a GIN index will most likely make it slower. With your original table this multicolumn index with the right sort order should be a ...


4

Note that in PostgreSQL a user and a role are the same thing. The difference is that a user can log in and a role cannot. So, to answer your question you need to decide if you want crm_usr to be able to login or not. The approach that I try to take is to have two (or more) roles-- one for owning (i.e. xyz_owner) the objects and one or more (xyz_user) for ...


4

Your current idea of using a range query on the primary key is probably the best bet. I don't see how partial indexes could help with that. You would need a series of partial indexes which would in aggregate be a total index, and with BTREE indexes used for range queries that would be pointless. But, the success of this method is likely to depend on the ...


4

You need to use pg_index and limit to those that have indisunique. To get the actual index and table names, you need to join that table to pg_class and pg_namespace: select idx.relname as index_name, insp.nspname as index_schema, tbl.relname as table_name, tnsp.nspname as table_schema from pg_index pgi join pg_class idx on idx.oid = ...


4

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 :)


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

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


4

Yes, Postgres allows settings per user or even per database and user, including statement_timeout: ALTER ROLE foo SET statement_timeout = 12345; -- milliseconds Related: How does the search_path influence identifier resolution and the “current schema” To see the currently active setting for the session: SHOW statement_timeout; Or get more details from ...


3

Try adding the partition key explicitly (redundantly!). Like: SELECT * FROM entries WHERE identifier = 'some_identifier_from_subtable_s' AND lower(left(identifier, 1)) = 's' -- 1st letter of above identifier LIMIT 1; This should allow Postgres to understand it can prune all other partitions from the query. Can be derived from $1 of course: AND ...


3

Yes, you can create a unique index for that: create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );


3

I don't know of a better solution in general. A few minor things, though: pg_get_serial_sequence() If you don't know the name of the parent's implicit sequence, use pg_get_serial_sequence(). SELECT pg_get_serial_sequence('data.log', 'id'); You might even use the expression in the CREATE TABLE script directly, but that would impose a very minor additional ...


3

The problem is with the parameter assignment in the call. To fix: CALL config.sp_add_holiday(holiday_date => '2018-01-01', name => 'New Years Day'); db<>fiddle here Explanation Parameter notation is the same for functions and procedures (new in Postgres 11). The manual: See Section 4.3 for the full details on function and procedure call syntax, ...


3

The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work. The reason is quite trivial. B-tree indexes do no support the containment operator @>. Neither for range types like tstzrange nor for any other type (including array types). The manual: ... a btree operator class ...


3

I expected the trigram index to figure out immediately there's no second 0 string anywhere 'second' and '0' are separate words, so it cannot detect their joint absence as such. It seems like it could detect the absence of ' 0', but this comment from "contrib/pg_trgm/trgm_regexp.c" seems pertinent: * Note: Using again the example "foo bar", we will not ...


3

If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring). However if the target database contains tables (or other objects) that were not contained in the source, those ...


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

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 ...


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