PostgreSQL version 9.4
PostgreSQL 9.4 is no longer supported and the product is EOL.
Specifically for PostgreSQL version 9.4
See the PostgreSQL tag for more info.
Questions tagged [postgresql-9.4]
498 questions
54
votes
2 answers
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
id serial primary…
Ced
- 754
- 2
- 7
- 10
52
votes
2 answers
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
xaxis integer NOT NULL,
value integer NOT…
user4150760
- 1,129
- 3
- 14
- 20
36
votes
2 answers
PostgreSQL joining using JSONB
I have this SQL:
CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES
('{"parent":null,"children":[2,3]}'),
('{"parent":1, "children":[4,5]}'),
('{"parent":1, "children":[]}'),
('{"parent":2, …
Kokizzu
- 1,403
- 6
- 18
- 35
35
votes
1 answer
Query JSON array of obejcts against multiple values
I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.
Given this example table:
CREATE TABLE grp(d jsonb NOT NULL);
INSERT INTO grp VALUES
…
BartZ
- 453
- 1
- 4
- 4
34
votes
5 answers
How to examine PostgreSQL server's SSL certificate?
Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate?
I'm hoping for output similar to what you would get from running openssl x509 -text .... And…
csd
- 700
- 1
- 6
- 11
34
votes
4 answers
Query the definition of a materialized view in Postgres
I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:
SELECT * FROM information_schema.views WHERE table_name = 'some_view';
which…
Sean the Bean
- 600
- 1
- 4
- 11
33
votes
2 answers
Does Postgres preserve insertion order of records?
For example when I'm using query which returns record ids
INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;
Which produce output:
1
2
3
Will this ids point to corresponding inserted values?
1 -> name1
2 -> name2
3 -> name3
Sergey
- 433
- 1
- 4
- 4
33
votes
2 answers
Database "frozen" on ALTER TABLE
Our production environment just froze* this morning for a while when altering a table, adding a column actually.
Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];
* Login into our system requires a select from that very…
Gonzalo Vasquez
- 1,059
- 2
- 18
- 33
31
votes
2 answers
"Recheck Cond:" line in query plans with a bitmap index scan
This is a spin-off from comments to the previous question:
Postgres 9.4.4 query takes forever
Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query plans output by EXPLAIN.
Like in the EXPLAIN output…
Erwin Brandstetter
- 185,527
- 28
- 463
- 633
29
votes
5 answers
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
Fabrizio Mazzoni
- 1,976
- 5
- 22
- 32
28
votes
1 answer
How do I split a long PL/pgSQL line of code over multiple lines?
Is there a way to split a long line of PL/pgSQL code over multiple lines? My context is a trigger function where I log inserts into a table as per:
INSERT INTO insert_log (log_time, description)
VALUES (
now()
, 'A description. Made up of 3…
dw8547
- 947
- 3
- 11
- 24
27
votes
1 answer
Index not used with = ANY() but used with IN
Table t has two indexes:
create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);
insert into t (a,b)
select i, i
from generate_series(1, 100000)…
Clodoaldo
- 1,145
- 2
- 8
- 22
26
votes
1 answer
How do I decompose ctid into page and row numbers?
Each row in a table has a system column ctid of type tid that represents the physical location of the row:
create table t(id serial);
insert into t default values;
insert into t default values;
select ctid
, id
from t;
ctid | id
:---- |…
Jack Douglas
- 40,517
- 16
- 106
- 178
25
votes
2 answers
How to get particular object from jsonb array in PostgreSQL?
I have a field called 'user' that holds a json array that roughly looks like this:
"user":
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
Now I want a query like:
select count from tablename where id = "1"
I'm not able to get the…
Rabi C Shah
- 251
- 1
- 3
- 3
21
votes
2 answers
psql: FATAL: Peer authentication failed for user
I just installed PostgreSQL 9.4 on Ubuntu 15.10.
I created a user with createuser -P myuser
I created a database with createdb -O myuser mydatabase
I edited pg_hba.conf and added local mydatabase myuser md5
I restarted PostgreSQL with sudo service…
Daniel
- 327
- 1
- 2
- 7