Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
7
votes
1answer
367 views

Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?

(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?) PostgreSQL 9.5. I can't divulge full details, but table has 22 columns and 5 indexes:...
13
votes
1answer
6k views

How to compare xmin and txid_current() after transactions ID wraparound?

Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer ...
5
votes
1answer
1k views

Why does this implicit join get planned differently than an explicit join?

In this answer I explain SQL-89s implicit syntax. But I noticed different query plans while playing around: EXPLAIN ANALYZE SELECT * FROM (values(1)) AS t(x), (values(2)) AS g(y); ...
17
votes
6answers
78k views

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
21
votes
1answer
2k views

Postgres: How is SET NOT NULL “more efficient” than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit ...
7
votes
1answer
28k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
4
votes
3answers
2k views

Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery? [duplicate]

I have a query that gets about 80k rows from said table with about 3m. I only need to paginate ten rows at a time, but with ORDER BY (a+b) DESC (a and b are integer type columns.) Right now my query'...
2
votes
1answer
2k views

Postgres 9.5 foreign table inheritance not using indexes

In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
2
votes
2answers
14k views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
4
votes
3answers
2k views

PostgreSQL importing thousands of columns as an array

PROBLEM: I have a CSV file with 16,382 columns with data that looks like this: +-------------+-----------+------+-------+------+------+------+-----+-------+-----+ | PATIENT_ID | DIAGNOSIS | 1 | ...
3
votes
1answer
12k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
27
votes
2answers
75k views

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select pk_a,...
20
votes
2answers
16k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
21
votes
2answers
17k views

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
21
votes
2answers
27k views

Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters ...
8
votes
2answers
4k views

Grouping on any one of multiple columns in Postgres

Is it possible to create some sort of chain of grouping in Postgres? Let's say I have the following chart: CREATE TABLE foo AS SELECT row_number() OVER () AS id, * FROM ( VALUES ( 'X', 'D', 'G', 'P'...
6
votes
2answers
16k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
5
votes
2answers
346 views

Why do my queries take longer together than when I run them separately?

I have a series of update statements that follow a general pattern: one update aggregates values from another table (or sometimes multiple tables), the next update produces a rank based on the ...
6
votes
2answers
5k views

What is the best way to create Primary Key on a large table in Postgresql 9.5?

I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock ...
4
votes
2answers
961 views

PostgreSQL filter/aggregate performance fluctuates depending on condition value

I'm experiencing a strange variation of filter performance depending on the value in WHERE condition with this query: SELECT MIN(revenue) AS min_revenue, MAX(revenue) AS max_revenue FROM ...
4
votes
1answer
75 views

Why does PostgreSQL scan a lot of rows?

I have a small table of 135,000 rows. Here is the schema: id integer NOT NULL nextval('history_sum_id_seq'::regclass) zone_id integer NOT NULL spot_id character varying(24) NULL::...
3
votes
2answers
2k views

Effectively searching through entire 1 level nested JSONB [closed]

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
5
votes
1answer
6k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
5
votes
1answer
10k views

Alter default privileges for role

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. Problem ...
4
votes
0answers
35k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
3
votes
2answers
2k views

Postgresql large transaction OOM killer

We have a Postgresql 9.5 running on CentOS release 6.5 (Final) The server has 16GB of ram. We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides ...
3
votes
1answer
3k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
1
vote
2answers
3k views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
1
vote
0answers
48 views

Possible for two simple updates on the same table to deadlock?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...
0
votes
1answer
3k views

PostgreSQL: Multiple Inheritance tables (one child to two parents)

If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect. However what I want to know ...