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

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

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


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


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

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


3

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


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

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


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

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

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

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

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


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

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

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


2

In general PostgreSQL is capable of using an index in this situation. It can scan the index two times and combine them with a BitmapOr. The reason it cannot or has chosen not to do this in your particular case would depend on the particulars of your case, which you are not sharing with us. Perhaps it just thinks the alternative plan will be cheaper.


2

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


2

Different parts of the index need to examined based on what trigrams are present in the LIKE pattern, and then different parts of the table need to be examined both to recheck the pattern for false positives, and to get the other columns you selected for the true positives. The first time for a given pattern, it needs to fetch large those parts of the index ...


2

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


2

You can use a self-join for that: select ni.date, ni.stock_id, ni.report_value as net_income, nr.report_value as total_revenue from report_items ni join report_items nr on ni.stock_id = nr.stock_id and ni."date" = nr."date" and nr.report_item = 'totalRevenue' where ni.report_item = 'netIncome' order by date desc limit ...


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


1

Jeff provided the explanation you were looking for. Phrase search as provided by the text search infrastructure might be your solution. CREATE INDEX test5_keyvalues_ts_idx ON test5 USING GIN (to_tsvector('simple', keyvalues)); Query: SELECT * FROM test5 WHERE to_tsvector('simple', keyvalues) @@ phraseto_tsquery('simple','first 1 second 3'); AND ...


1

In the special case of an unindexed table, yes, SELECT can do the same work as VACUUM (as far as removing dead rows is concerned).


1

I presume the difference between you running your queries via psql vs. from a Java program is that you use prepared statements in Java. Processing "simple statements" and prepared statements take different code paths in the Postgres backend (postgres.c). "query-start" probe is only called when a simple statement is executed (exec_simple_query), while a ...


1

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.


1

Your understanding is almost but not quite correct. True, every btree index tuple needs a ctid (or some form of block number and tuple index) to point to the heap tuple (table row). But (at least) since the introduction of "heap-only tuples" with Postgres 8.3, there may be a HOT chain to follow to arrive at the current live tuple in the snapshot - with a ...


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