Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
11
votes
1answer
5k 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 ...
4
votes
1answer
7k 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 = ...
10
votes
2answers
5k 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 ...
1
vote
1answer
150 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 ...
5
votes
1answer
491 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
7k 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
4answers
4k 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 ...
5
votes
1answer
3k 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) ...
4
votes
2answers
1k 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
833 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 ...
4
votes
1answer
643 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
5k 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, ...
3
votes
1answer
115 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 ...
1
vote
1answer
66 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
1k 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
2answers
104 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 ...
1
vote
1answer
629 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
1answer
432 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 ...
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 ...
0
votes
2answers
4k 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 ...