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


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


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

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

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


3

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


3

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


3

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.


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

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

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


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

Handling of date and timestamp input has been made stricter in Postgres 10. Quoting the release notes: Make to_timestamp() and to_date() reject out-of-range input fields (Artur Zakirov) For example, previously to_date('2009-06-40','YYYY-MM-DD') was accepted and returned 2009-07-10. It will now generate an error. Explains what you observed. You'...


2

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


2

As far as my research goes, postgresql is not able to rewrite containment check into an expression that could be matched using btree index, i.e. esl1.created_at >= now() - interval '1 hour' AND esl1.created_at < now() + interval '1 hour' when written such way, the query is executed using indexes: Index Scan using event_seating_lookup_created_at_idx ...


2

This is all around a horrible schema. You shouldn't be using json (as compared with jsonb) at all, ever (practically). If you're querying on the field, it should be jsonb. In your case, that's still a bad idea though, you likely want an sql array.. CREATE TABLE raw ( raw_id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, data int[] ); ...


2

recovery_target_time='2018-01-06 16:22:00' This 2019, not 2018. If you look in the server log file, you will probably find the recovery stopped as soon as it could (as soon as it reached a consistent state), because it could not stop as soon as you told it to, being a year late for that.


2

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


2

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'),...


2

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.


2

As far as I'm aware what you're trying to do, by issuing a commit inside a procedure, is use an autonomous transaction. Postgres does not support them inside stored procedures/functions, and the behaviour you are seeing is correct - the (implicit) transaction does not complete until the procedure completes. This page discusses the issue in detail along with ...


2

Assuming a mostly immutable set ~ 100 currencies overall (you haven't been clear on that), and your given requirements, consider the simple approach: 1 table with 1 row per user and 1 column per currency. Like: CREATE TABLE wallet ( user_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY , currency1 integer -- or numeric, depends on missing info , ...


2

WITH RECURSIVE cte AS ( SELECT id, domain_name, valid FROM domains WHERE parent_id IS NULL UNION ALL SELECT domains.id, domains.domain_name, domains.valid FROM domains JOIN cte ON domains.parent_id = cte.id WHERE NOT cte.valid -- stop recursion when valid node reached ) SELECT id, domain_name FROM cte WHERE valid fiddle


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