All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
0answers
3 views

Is it possible to migrate a postgresql database without downtime?

I have a Postgresql Database on my Centos, I use barman to backup my database, my question is that is it possible to migrate my database to another server without downtime? I know some solutions with ...
0
votes
0answers
6 views

Is it possible to change pgAdmin SQL completion shortcut?

In pgAdmin SQL query editor, the default SQL code completion shortcut is ctrl-space; however, my native language is not English, we use ctrl-space to switch input method. Is it possible to change ...
0
votes
1answer
16 views

Sending an async notification to another database in PostgreSQL

Is there any way I can send a NOTIFY to a database other than the one that my connection has specified? [CLARIFICATION] Consider we have two databases within one instance of PostgreSQL. If you are ...
0
votes
1answer
19 views

Cleanup after deleting a large table

I have a database that once held a large table: ~6 billion rows, ~6TB of disk space, ~10 rows inserted/second. The rest of the tables, all told, comes to ~500MB and have infrequent writes. So the big ...
0
votes
0answers
6 views

How to transfer DDL changes between different stages?

What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?
4
votes
1answer
483 views

Upgrade from Postgres 9.3 to 9.5

I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate ...
6
votes
1answer
12k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
19
votes
2answers
12k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
0
votes
0answers
12 views

Postgres: Refer to several other id's in a certain row

I am a postgres newbie, I struggle to execute this: I have a table of artists and their info (birth, death, style, etc..) Each row has a unique id_name varchar column has values like jones or adams ...
1
vote
1answer
496 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
2
votes
0answers
18 views

Implementing faceted search in PostgreSQL

I'm trying to figure out if it is possible to implement a faceted search (like the ones popular in online shops like Amazon) in a reasonably sane and efficient way in PostgreSQL. A faceted search ...
5
votes
2answers
919 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
1
vote
0answers
13 views

Inserting rows from one multimillion rows table to another

I have a transactional (OLTP) database with tables that have about 100 million rows. I have on another server a data warehouse with similar size tables. I would like to update the data warehouse close ...
1
vote
0answers
7 views

Shouldn't DISCARD PLANS re-evaluate every function definition in postgres?

Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view. ...
0
votes
0answers
5 views

postgres create a view with data transformation rules

I was hoping someone could point me to a resource that can help me solve my problem. I have a largish dataset (25 Million rows), and I need to export the table based on a set of rules. 6 rules to ...
0
votes
1answer
18 views

Postgres very slow on server

Restoring a single table on the server is very slow From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016 Server 2016-07-22 13:55:11 CEST [88719-1] tester@test LOG: duration: 22988.122 ...
4
votes
1answer
30 views

Function that returns table with an additional column

I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)? For example, ...
-1
votes
0answers
24 views

Insert into the Table using PGSQL — C lib — Runtime [on hold]

I want to insert data into a table of a database using PSQL-- C interface I was able to do it in hard code manner as follows: res = PQexec(conn, "INSERT INTO t_name VALUES(c1_val,c2_val,…)"); But I ...
0
votes
1answer
19 views

Best way to move different desktop local PostgreSQL DB to single amazon RDS

I have system which is having java Desktop application with PostgreSQL which is installed on 5 machine each is having individual database and having databases into same database structure. Now I want ...
0
votes
0answers
17 views

when restoring with pg_restore got errors

I get a few errors below while restoring a database dump with pg_restore, i was able to do this following the previous steps in 9.2 (i got a new ubuntu and upgraded to postgres 9.5), is there any ...
0
votes
0answers
18 views

Live Syncing Data Between PostgreSQL and MySQL clusters

I have a unique situation to solve. My company is switching over to an app that runs on a Postgres backend. The app is replacing a current set of apps that run on a MySQL cluster. However, our initial ...
0
votes
1answer
35 views

PostgreSQL indexing on bit string

What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the ...
0
votes
0answers
14 views

why execution time change between each run

I have a pgAgent job connecting to a remote server: Every minute bring ~400 records. Job take ~500 ms Result are save in avl_pool (avl_id, x, y, azimuth, near_link) last one is null here I ...
0
votes
1answer
13 views

postgres btree index on large text column

I have a table with big text column on which I want to enforce uniqueness, postgres can only do this with a btree index. I get the following error : CREATE unique INDEX idx_protein_seq_sequence ON ...
0
votes
0answers
18 views

Count number of indexes usage for select

I have a lot of indexes. Some of them are not used (because they created once). But I want to know why it is not used. I have the following tables and indexes: create table statuses ( id ...
0
votes
1answer
24 views

Why does restoring postgresql database from dump produce errors

I did the following commands: pg_dumpall --clean | gzip > db.gz gunzip db.gz psql < db I expected the third command (loading the database back) to just run with zero errors, but I got some ...
3
votes
2answers
126 views

Why some rows are missing in my recursive CTE?

These are my 2 tables id_hierarchy and hierarchy_link: CREATE TABLE id_hierarchy ( id integer NOT NULL, hierarchy integer ); insert into id_hierarchy values (11,2),(22,3),(44,5),(77,8),(170,...
0
votes
0answers
11 views

PostgreSQL 9.5, getting “cached plan must not change result type” error

I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in ...
1
vote
1answer
38 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 ...
0
votes
1answer
47 views

Pause postgres process already in progress

A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has ...
1
vote
0answers
15 views

Why can read only users create objects inside postgres database? [duplicate]

I've just started working with Postgres and I noticed the following. If I create a user they automatically have the ability to do whatever they want inside the database. User was created with: ...
1
vote
0answers
42 views

GUI tools for querying PostgreSQL [on hold]

I have tried Psequel, pgAdmin and plain old psql on OSX for querying against Postgres. In my experience, all these free third-party tools are incredibly buggy and pale in comparison with SQL Server ...
0
votes
1answer
25 views

Join query result of 2 different tables based on common column

I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it....
15
votes
1answer
2k views

What is the special database “postgres” for?

I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never ...
0
votes
3answers
62 views

Is there a symbol I can use to have a where clause match all columns?

I'm using sql parameter's and I have a query that I'd like to use for two similar queries. The first uses two where clauses the other uses only one of the clauses is there a symbol I can use for the ...
0
votes
1answer
16 views

PSQL - Selecting all parents and children [on hold]

I have a one to many and I am wanting to grab all of the records from table1 and all of the relationships for the record. The closest query I have gotten to this is SELECT t1.*, table2.* from table1 ...
0
votes
1answer
34 views

Select rows based on a minimum in one column

Working with PostgreSQL 9.4, I want a result that takes into consideration only min() (all aggregate functions) values of distance on JOIN or WHERE. But its seems those things are not allowed. So, I ...
1
vote
1answer
19 views

Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian

After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system. I want to have 9.4 serve my data and get rid of the outdated version. AFAICS, it is important to use a ...
1
vote
0answers
24 views

How to design efficient queries and structure for data logging table (numerical stats)

I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience ...
7
votes
4answers
10k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes --------+-----------...
0
votes
0answers
18 views

query cost in postgresql using explain

I read the following link and try to understand the output. https://www.postgresql.org/docs/9.1/static/sql-explain.html EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; ...
1
vote
2answers
310 views

How to update the parent/child of all rows in the tree (ltree)?

Here are my configurations: DB type: postgresql (9.3 if that's necessary) Table name: product_sections Column names: section_id (integer) & section_path (ltree) Detail: I have one reference ...
1
vote
0answers
16 views

How to give select privileges to database in postgreSQL [duplicate]

How can we give select access at database level in postgreSQL. At present I had created new user with select access on all tables in a schema using below syntax. grant select on all tables in ...
0
votes
1answer
17 views

how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? [duplicate]

I follow this URL to make some SOP for my colleague. How to update the parent/child of all rows in the tree (ltree)? However, my colleague hope to use some simple statement about "UPDATE" for adding ...
0
votes
1answer
21 views

Partition elimination and date variables

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
1
vote
4answers
64 views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
0
votes
1answer
31 views

PostgreSQL use NEW in query for INSTEAD OF trigger

I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario: CREATE TABLE Product ( product_id ...
0
votes
1answer
16 views

Using varchar_pattern_ops in a multi-column index in Postgres

I'm using postgres 9.5 If I have a table with 2 columns like so: CREATE TABLE mystuff ( somestring character varying(256), timestamp_ timestamp without time zone NOT NULL ) Will this multi-...
0
votes
0answers
18 views

Super polymorphic event schema

I want to be able to track every action a user takes on my site. An action can originate from a visitor or a user (both of which are human). An action can affect a subject (visitor or a user) An ...
1
vote
1answer
31 views

Speed and size with hidden index in postgresql

I have a table with 1M rows without index, and i don't understand why when i go for : SELECT * from table WHERE id = 954000 i get the result instantly ! I have no index how its possible to get me ...