43
votes
Accepted
How to store one-byte integer in PostgreSQL?
No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint,...
- 180k
35
votes
Accepted
Drop table taking too long
You can list the running backends with
SELECT * FROM pg_stat_activity;
identify the process trying to rollback something which is working on this table.
Find its pid.
You can terminate a backend ...
- 2,109
32
votes
Accepted
Try catch equivalent in Postgres
The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR ...
- 23.8k
28
votes
Accepted
Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6
What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and ...
- 64.1k
27
votes
Accepted
How to make DISTINCT ON faster in PostgreSQL?
For only 400 stations, this query will be massively faster:
SELECT s.station_id, l.submitted_at, l.level_sensor
FROM station s
CROSS JOIN LATERAL (
SELECT submitted_at, level_sensor
FROM ...
- 180k
23
votes
How to verify SSL always being used on Postgresql 9.6
Non-SSL connections can be disabled through pg_hba.conf.
For instance, it may start like this:
# allow local connections through Unix domain sockets
local all all peer
# allow non-encrypted ...
- 31.9k
22
votes
Accepted
Understanding where pg_toast is coming from
You can use this query to find out all tables with TOAST tables:
SELECT oid::regclass,
reltoastrelid::regclass,
pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind ...
- 54.8k
20
votes
Accepted
How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)
The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can ...
- 1,094
19
votes
Accepted
WAL archive: FAILED (please make sure WAL shipping is setup)
When running into an error like this, be sure to run barman switch-xlog --force --archive <server_name> as noted in the barman documentation to verify the WAL archiving process.
- 3,669
19
votes
Accepted
What index to use with lots of duplicate values?
BTree
My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end ...
- 40.1k
19
votes
Accepted
Postgres choosing a filter instead of index cond when OR is involved
Fortunately this is very simple in PostgreSQL as it supports comparisons between “row values” (or compound values) that can make use of an index.
So you can write:
WHERE (timestamp, id) &...
- 54.8k
17
votes
Accepted
Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. It's no insurance against ...
- 180k
16
votes
Accepted
PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs
Probable bug on 9.6 and 9.6.1
This completely looks like a bug to me...
I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (...
- 13.5k
15
votes
Accepted
Count NULL values per row
1. You know the column names
For Postgres 9.6 or later, use num_nulls()
SELECT id, num_nulls(a,b,c,d,e)
FROM tbl;
Returns your desired result exactly, for any mix of data types.
The manual:
...
- 180k
14
votes
Accepted
SELECT FOR UPDATE locking other tables in PostgreSQL
We also don't ever update primary keys.
In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking:
FOR NO ...
- 98.5k
13
votes
self-joins in recursive CTE: `recursive reference must not appear more than once`?
Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple ...
- 231
13
votes
Accepted
Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?
The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly.
Separate columns beat a ...
- 180k
13
votes
Accepted
Accessing the OLD/NEW table value in a trigger function in plain SQL
Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine).
A data change trigger is declared as a function with no ...
- 1,982
13
votes
Accepted
SELECT DISTINCT ON, ordered by another column
You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See:
Get distinct on one column, order by another
PostgreSQL DISTINCT ON with different ORDER BY
SELECT *
FROM ...
- 180k
12
votes
Accepted
Postgresql: How to index jsonb array of objects column
The following code will be able to use a GIN index on the jsonb column:
SELECT COUNT(*) FROM results
WHERE subjects @> '[{"subject": "B/MATH", "grade": "A"}]'
The difference to your example is ...
- 1,220
12
votes
Trigger to update a materialized view once after relevant transaction?
An OK solution using triggers
This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.
(In the ...
- 925
11
votes
Accepted
Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)?
Not likely. GROUP BY and ORDER BY typically entail a sort. However, in this case a HashAggregate is used (likely because we're working with the whole table).
CREATE TABLE foo AS
SELECT x % 5 AS a, x %...
- 64.1k
11
votes
Accepted
PostgreSQL/PostGIS 9.6 broke my compound index
You need to install a specific EXTENSION in your database:
CREATE EXTENSION btree_gist ;
According to PostgreSQL documentation on btree_gist:
btree_gist provides GiST index operator classes that ...
- 13.5k
11
votes
Accepted
Set PostgreSQL table to LOGGED after data loading
Problem
I believe currently SET LOGGED rewrites the table using the WAL (essentially doing the whole operation), and rewrites the indexes.
So I found a thread about this on the lists
A new ...
- 64.1k
11
votes
Accepted
How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?
See the docs, you should be able to access the old and new records from a statement trigger:
CREATE TRIGGER some_table_update_trigger
AFTER UPDATE ON some_table
REFERENCING NEW TABLE AS newtab ...
- 356
11
votes
Is DEFAULT NULL and 'nullable value without default' completely the same?
No. It's not completely the same. NULL is just the default default (sic!) of data types.
All built-in data types shipped with Postgres 15 have NULL as default1. But that does not necessarily apply to ...
- 180k
10
votes
What does this error mean when doing a pg_restore on a Postgres 9.6.2 database?
The database the dump file was taken from has a certain foreign key constraint defined. But your target database does not. Probably because someone went and added it to the dumped database since the ...
- 40.6k
Only top scored, non community-wiki answers of a minimum length are eligible