PostgreSQL version 9.6
Questions tagged [postgresql-9.6]
513 questions
35
votes
2 answers
How to store one-byte integer in PostgreSQL?
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value and I want it to be stored in a one-byte data…
ukll
- 781
- 1
- 7
- 13
26
votes
1 answer
Try catch equivalent in Postgres
Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
FastTurtle
- 363
- 1
- 3
- 8
23
votes
2 answers
How to make DISTINCT ON faster in PostgreSQL?
I have a table station_logs in a PostgreSQL 9.6 database:
Column | Type |
---------------+-----------------------------+
id | bigint | bigserial
station_id | integer …
Kokizzu
- 1,403
- 6
- 18
- 35
22
votes
3 answers
What index to use with lots of duplicate values?
Let's make a few assumptions:
I have table that looks like this:
a | b
---+---
a | -1
a | 17
...
a | 21
c | 17
c | -3
...
c | 22
Facts about my set:
Size of the whole table is ~ 1010 rows.
I have ~ 100k rows with value a in column a,…
foo
- 323
- 1
- 2
- 6
21
votes
1 answer
Can the 'returning' clause return source columns that are not inserted?
Here's a minimal example of my real-world problem:
create table t(id serial primary key, rnd double precision);
of course you can return the inserted columns with a returning clause:
with w as (insert into t(rnd) values(random()) returning…
Jack Douglas
- 40,517
- 16
- 106
- 178
20
votes
2 answers
How to verify SSL always being used on Postgresql 9.6
I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers.
Although I believe my postgresqls are configured to always use ssl I need a way to double-check this.
Is there…
David Simic
- 301
- 1
- 2
- 3
19
votes
1 answer
Drop table taking too long
I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time.
It's been running for 2 days now. I suspect the rollback function to be the reason for this.
Is there a way to avoid the…
Pelle G
- 193
- 1
- 1
- 6
19
votes
1 answer
Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6
I've a trouble with PostgreSQL 9.6 query planning. My query looks like this:
SET role plain_user;
SELECT properties.*
FROM properties
JOIN entries_properties
ON properties.id = entries_properties.property_id
JOIN structures
ON structures.id =…
Yury Lysogorskiy
- 193
- 1
- 1
- 4
18
votes
1 answer
Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
Setup
I have a bulk insert function set_interactions(arg_rows text) that looks like this:
with inserts as (
insert into interaction (
thing_id,
associate_id, created_time)
select t->>'thing_id', t->>'associate_id', now()…
Kev
- 474
- 1
- 4
- 15
16
votes
2 answers
SELECT DISTINCT ON, ordered by another column
Please consider the following table test:
CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
(1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,'xyz','2015-09-13')
, (3,'tcs','2015-01-15')
,…
Luis
- 347
- 2
- 3
- 9
16
votes
3 answers
Postgres speed up index creation for large table
I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever for the b-tree indexes to be created. For one of…
Amy
- 373
- 1
- 2
- 7
16
votes
3 answers
How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)
I'm querying to the system catalog in Postgresql 9.6.4
Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to find the type of index anywhere in the system…
maxTrialfire
- 1,194
- 4
- 11
- 23
16
votes
1 answer
Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)?
Let's say i have a table with 3 columns a,b and c.
Could i possible speed up a query that looks like this by using index(es) ??
SELECT a,b,SUM(c) # or AVG(c)
FROM table
GROUP BY a,b
ORDER BY a,b
;
If the above question is positive, what type of…
Moras
- 189
- 1
- 2
- 10
15
votes
1 answer
Is there a query to check the current WAL size in PostgreSQL?
I'm using a hosted PostgreSQL database where I don't have shell access. Is there a query I can use to check the current WAL log size?
Antonius Bloch
- 263
- 1
- 2
- 7
15
votes
4 answers
PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs
If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method:
Introduce 2 new columns as C and D2.
Copy the contents of D to D2.
Delete D.
Rename…
Andy
- 593
- 3
- 12