Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

11
votes
1answer
2k 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 ...
6
votes
1answer
126 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:...
5
votes
1answer
384 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); ...
6
votes
3answers
23k 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 ...
13
votes
1answer
1k 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 ...
4
votes
1answer
6k views

Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$. $do$ BEGIN FOR yyyy in 2012..2016 THEN ...
3
votes
3answers
600 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'...
1
vote
1answer
8k 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 ...
5
votes
2answers
288 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 ...
4
votes
1answer
11k 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 ...
3
votes
2answers
367 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
3answers
783 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
3k 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 --------- {} {} {}...
3
votes
1answer
3k 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 ...
2
votes
0answers
253 views

Pgpool II and Postgresql upgrade scenario using different major versions

Pgpool FAQ states that: You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x And the reason that I understand very well is: Pgpool-II assumes messages from ...
1
vote
1answer
1k 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 ...
1
vote
2answers
295 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
1answer
1k 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 ...
0
votes
1answer
980 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 ...