Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
15
votes
4answers
1k views

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....
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 (...
13
votes
2answers
5k views

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

Very slow simple JOIN query

Simple DB structure (for an online forum): CREATE TABLE users ( id integer NOT NULL PRIMARY KEY, username text ); CREATE INDEX ON users (username); CREATE TABLE posts ( id integer NOT ...
11
votes
1answer
6k 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 ...
11
votes
1answer
5k views

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....
11
votes
1answer
713 views

Partition constraint not used for joins involving tables partitioned by timestamp

I have a partitioned table structure like: CREATE TABLE measurements ( sensor_id bigint, tx timestamp, measurement int ); CREATE TABLE measurements_201201( CHECK (tx >= '2012-01-...
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 ...
8
votes
1answer
484 views

PostgreSQL/PostGIS 9.6 broke my compound index

In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't ...
8
votes
1answer
393 views

Slow query times for similarity searches with pg_trgm indices

We added two pg_trgm indices to a table, to enable fuzzy searching by either email address or name, as we need to find users by name, or email addresses which have been misspelled during signup (e.g. "...
8
votes
0answers
521 views

Why does RDS PostgreSQL always recover on restart?

I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...]. I have found that after issuing a stop from the RDS console followed by a ...
7
votes
2answers
162 views

Postgres Transaction OOM on 100k DDL statements

We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...
6
votes
1answer
2k views

Diagnosing high write throughput on RDS Postgres

I'm using Postgres 9.6.1 on RDS, and seeing orders-of-magnitude higher write throughput than I expect to see. I'm seeing a WriteThroughput around 40,000,000 Bytes/Second. I've looked at the ...
6
votes
1answer
131 views

Postgresql is failing to use my covering index and falling back to a much slower bitmap scan

I'm trying to figure out why a table of mine is using a Bitmap Heap Scan when a index scan is dramatically faster. Table: webarchive=# \d web_pages ...
6
votes
1answer
979 views

Postgres 9.6 export the database structure as pdf [closed]

Is there any tool/software the can export all the tables structure (column names , its datatype ) in the database as a pdf/excel/word. additional diagram export will be added advantage.
6
votes
1answer
3k views

How to pass value to ON CONFLICT clause that's not a column?

Basically, I'm trying to do multiple inserts in a single command in postgresql, where my ON CONFLICT has a per-insert parameter. Here is the query (formatted for psycopg2): INSERT INTO web_pages ...
6
votes
1answer
239 views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
6
votes
2answers
5k 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 ...
6
votes
2answers
2k views

Postgres slow query with order by, index and limit

I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows. Column | Type | Modifiers -------...
6
votes
3answers
10k views

How do I change Postgresql to be listening on port 5432?

I've upgraded Postgresql from 9.3 to 9.6 on my machine (linux mint rafaella- so ubuntu 14.04) As the previous version was listening on 5432 the new version is listening on 5433, but I want to change ...
5
votes
4answers
5k 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) ...
5
votes
1answer
523 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 ...
5
votes
2answers
2k views

Aggregate sales of the past 12 months for the current row date

I need to compute the trailing 12 months sum of sales for a given client_id, based on each row given month. Here is the initial table in a month by month aggregated sales by client (filtered here on ...
5
votes
2answers
6k views

How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data?

When I tried to run psql, I got psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?...
5
votes
2answers
2k views

Postgresql 9.6 best settings for large admin work (min_wal and max_wal)

We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a pg_restore of a simple database dump of less ...
5
votes
2answers
7k views

How to get psql to prompt for a password with the superuser postgres?

I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with: psql -U myusername it asks for a password just fine and logs in. However if I do: psql -U postgres it'...
5
votes
1answer
678 views

Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error. create or replace ...
5
votes
1answer
1k views

How exactly does trigram word-similarity work?

The docs on the word_similarity function say: Returns a number that indicates how similar the first string to the most similar word of the second string. The function searches in the second string ...
5
votes
1answer
246 views

Indexes not used with OR

I have query such as this: SELECT t0.id FROM platform_conversations t0 LEFT OUTER JOIN contacts t1 ON t1.id = t0.contact_id WHERE t0.user_id = 5340 AND ( t0.participant ILIKE '%baa%' -- (1) ...
5
votes
1answer
1k views

Refresh Materialized View impact on the DB

Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (...
5
votes
0answers
206 views

UUID V1 and V4 bloats substantially after sequntial inserts and deletes. Why is it so, compared to serial and temporal BTree (which does not bloat)?

I am trying to understand the impact of deletions to bloat of tables and indexes after large number of inserts and deletes. The inserts and deletes are following a rather strict pattern: first the ...
5
votes
0answers
3k views

Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages. Postgres seems to only ...
5
votes
0answers
585 views

How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
4
votes
5answers
233 views

Postgres join where foreign table has ALL records

I have this people and tags table, like this, CREATE TABLE people AS SELECT * FROM ( VALUES (1,'Joe'), (2,'Jane') ) AS t(id,name); CREATE TABLE tags AS SELECT * FROM ( VALUES (1, ...
4
votes
2answers
610 views

Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value

Question How to improve this query to increase the speed of the query shown below from 30+ seconds to milliseconds? I am using PostgreSQL: v 9.6.6. Context I have a time series table buildings....
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 ,'...
4
votes
1answer
1k views

How 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 ...
4
votes
1answer
8k 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 = ...
4
votes
1answer
603 views

Foreign key on PostgreSQL range bounds

Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)? Currently I keep the lower bound and upper bound in two columns, ...
4
votes
1answer
1k views

Using host names in `pg_hba.conf`?

According to the PostgreSQL documentation: A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not ...
4
votes
2answers
2k views

PostgreSQL select count with dynamic date range

Firstly, I am new to this forum. I hope that someone can help or advise me on my issue. Given a User createdAt date and the current date. I would like to retrieve the sum of Friends records for the ...
4
votes
2answers
2k views

Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?

For example, say I have a table with a medium number of rows (~100,000 or so) that has a jsonb column with the following example data in one of the rows: {"name":"Bob", "favoriteColor":"red", "...
4
votes
2answers
723 views

How to completely hide a function from all other users and roles?

Database : Postgres 9.6 Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even want to expose even the name or body of function to other users/...
4
votes
2answers
232 views

PostgreSQL: Do regclass/oid columns persist across backups?

I have a large table (audit) in which I store relation ID of the schema and table. This is conveniently populated by trigger using TG_RELID. It is also convenient because I can index it and the index ...
4
votes
1answer
6k views

What does this error mean when doing a pg_restore on a Postgres 9.6.2 database?

So I have a rails 5.0.x application running on Heroku with Postgres 9.6.1. I pull down and do a pg_restore --exit-on-error --verbose --clean --no-acl --no-owner -h localhost -d database_development ~/...
4
votes
1answer
667 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 | ...
4
votes
1answer
466 views

What's actually happening in the “Result” node and “Append” in a Postgres plan?

I have a table (postgres 9.6) which is partitioned by date into about 70 child tables. The EXPLAIN output below is truncated as most of it is identical bitmap index/heap scans of each of the child ...
4
votes
1answer
7k views

Accessing the OLD/NEW table value in a trigger function in plain SQL

I am trying to build a trigger function to automatically update parent order values any time any of it's items changes. This is what I have created: CREATE OR REPLACE FUNCTION update_totals() ...
4
votes
1answer
98 views

Are PostgreSQL clusters and servers the same thing?

From the official documentation (emphasis mine): Chapter 22. Managing Databases Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost ...