Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
13
votes
1answer
7k views

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 ...
12
votes
2answers
7k views

How to make DISTINCT ON faster in PostgreSQL?

I have a table station_logs in a PostgreSQL 9.6 database: Column | Type | ---------------+-----------------------------+ id | bigint ...
4
votes
1answer
10k views

cannot insert multiple commands into a prepared statement

This query works for me when I tried in PG admin query tool: UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456, "UAN"=789,tax = 'myy' WHERE user_id = ...
5
votes
3answers
228 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
1
vote
1answer
462 views

How do I get Postgres DO $$ to work from the bash prompt?

Postgres 9.6.9 on Linux RHEL 6.10 If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail. So I switched ...
2
votes
1answer
2k views

PostgreSQL 9.1 and 9.4 faster than 9.6

We decided to migrate to 9.6 after some research about parallel query, so we brought up two identical machines with exactly the same hardware, kernel and others with PostgreSQL 9.1 and 9.6 ...
6
votes
1answer
635 views

Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

I use PostgreSQL 9.6. I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't ...
14
votes
1answer
8k views

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 (...
5
votes
1answer
4k views

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) ...
5
votes
4answers
6k views

Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice? It is common to use parameters ...
4
votes
2answers
2k views

Count NULL values per row

I would like to count the number of null's present per row in a table without enumerating column names. For example: WITH t as (VALUES (NULL ,'hi',2,NULL,'null'), ('' ,'hi',2,3,'test'), (NULL ,'...
1
vote
2answers
1k views

Set PostgreSQL table to LOGGED after data loading

I have created an empty UNLOGGED table to faster copy a large amount of data (over 1 Billion rows). Loading the data took around 4 hours. Now I want to set the table to LOGGED to make it safe for ...
6
votes
2answers
6k views

postgres sequence problems, manually created pids, and proper sequence resetting

Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created ...
4
votes
1answer
744 views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
3
votes
1answer
138 views

Repeat an action for X number of PostgreSQL tables

Note: The solution does not need be optimised for processing speed. It's supposed to be part of a setup phase and won't be run more than once. General problem I have X number of tables in a ...
3
votes
1answer
663 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
3
votes
1answer
7k views

Optimizing slow performance of simple SELECT query

I have an app called 'Links' where 1) users congregate in groups and add others, 2) post content for each other in the said groups. Groups are defined by a links_group table in my postgresql 9.6.5 DB, ...
2
votes
0answers
41 views

RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however ...
1
vote
1answer
595 views

Index by subkey in jsonb

I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and ...
1
vote
1answer
986 views

How to find out what operator class and access method should be used for an exclusion constraint?

Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table. From reading the docs carefully, I could get as far as this: create table subnets ...
1
vote
2answers
2k views

postgres user is not superuser

Im having some problems with my postgresql server. In particular, some databases cannot be dropped. On further inspection, I see that the postgres user is not a SuperUser. There are two other users ...
1
vote
1answer
68 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
1
vote
2answers
127 views

Optimizing a “categorized” search table using trigrams

I've been reading through this excellent answer to understand how pg_trgm works a bit, but I'm still unclear on the most efficient way of solving this query (efficient in terms of speed of search): I ...
0
votes
2answers
5k views

SQL command to clean job queue in pgAdmin 4

I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the ...
0
votes
1answer
1k views

ERROR: column X specified in USING clause does not exist in left table

I have a rather complex query I want to execute. The data on table import_temp_table is imported from a CSV file coming from Google Datastore and CouchDB. The table looks like this: CREATE TABLE ...