Tag Info

New answers tagged

1

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details: SELECT 'Inspections'::text AS data_label ,count(i.reporting_id) AS daily_count ,d.day AS date_column FROM ( SELECT day::date FROM generate_series(date '2013-01-01' , date ...


0

with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 3, interval '1 day' day) day ), inspection as (select date '2013-01-01' AS close_case_date UNION ALL select date '2013-01-03') select (SELECT COUNT(*) FROM inspection AS i WHERE i.close_case_date = d.day) AS daily_count, d.day as date_column from days d ...


0

The documentation says the following: ROW EXCLUSIVE Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any ...


3

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since: access_type ... is null in 90% of cases that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...


1

Do you need VACUUM FULL? VACUUM FULL is necessary to reclaim physical space after purging Yes, if you want to return that space to the OS or use it in other tables. If you expect to just re-use the space for new rows in the same table, you're better off just running normal VACUUM (or letting autovaccum do its job), so the blocks containing deleted ...


0

In PostgreSQL, when a user writes SELECT * FROM thetable, and thetable is a real table (not a view), it is not possible to restrict number of rows returned. It is possible for UPDATE/INSERT/DELETE (with triggers and shared memory).


1

Note the blog referenced in the answer given by @Gyre will not work for creating an "application" read-only user (i.e, for creating a read only role for a web application to connect to the database), and might open up a serious security vulnerability, since its easily circumventable, as explained in this postgresql list response. For reference, by the client ...


2

There is no built-in facility for this. If all queries execute on one connection you could use a debugger (gdb or msvc, depending on platform) to set a conditional breakpoint with a hit-count of 1000 in exec_simple_query and exec_execute_message. Otherwise you'll have to do it client-side. In general, wanting to do something like this suggests that ...



Top 50 recent answers are included