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

learn more… | top users | synonyms (1)

2
votes
0answers
42 views

Output from `pg_dump --schema-only` has inconsistent order

I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers: ...
1
vote
0answers
13 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
1
vote
1answer
33 views

Strange upper() behaviour for dateranges in PostgreSQL

I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query: select upper(('[2005-12-01,2005-12-04]')::daterange) It returns 2005-12-05 instead of 2005-12-04. The ...
2
votes
2answers
143 views

PostgreSQL data files have size more than data itself

We have a system that does some data archiving to a PostgreSQL DB. We found out that the PC storage was full due to the DB archiving. The problem is that I checked the data files residing in ...
4
votes
2answers
52 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
3
votes
1answer
53 views

Index not being used in PostgreSQL

I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2, and table2 has a column referencing table3. Both foreign key columns have a B-tree index ...
1
vote
1answer
22 views

Do data checksums apply to large objects?

PostgreSQL 9.3 introduced the data checksums feature (initdb -k). Do these checksums apply to large objects?
1
vote
1answer
67 views

How to reduce size of PostgreSQL table after dropping columns?

I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the ...
0
votes
1answer
21 views

Postgres constraint function on a single column

Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed? Right now, I have this: ALTER TABLE public.employee ADD CONSTRAINT ...
1
vote
1answer
41 views

Postgres 'could not write to hash-join temporary file: No space left on device'

I was getting this error a while ago and I was curious about two things - 1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in ...
2
votes
1answer
25 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
1
vote
0answers
69 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
1
vote
0answers
19 views

Pgbouncer server parameter

I installed and configured pgbouncer 1.5.4 on Ubuntu 14.04 LTS and I have a hard time interpreting log messages. Basically, I have seen many errors: 2015-12-10 18:09:16.835 30488 ERROR ...
0
votes
1answer
31 views

syntax error on mathematical operation postgresql

I'm trying to declare this function to be used as a trigger: create or replace function atualiza_qtfunc() returns trigger as $f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários ...
1
vote
2answers
54 views

need better db design for project

I have two tables: notes users Notes Table: id || description ||CreatedBy(which is userid)||......other columns --------------------------------------- Users table: id || name || ......other ...
0
votes
2answers
55 views

SQLAlchemy connection error with Postgres

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by ...
2
votes
1answer
60 views

Using an index on a Postgresql integer range causing trouble

Unfortunately I was not able to create a usable example / fiddle. But I am using a Postgresql 9.4 table that looks somewhat like this: CREATE TABLE table1 (id INT PRIMARY KEY NOT NULL, dtype TEXT, ...
0
votes
1answer
33 views

Postgres develops server doesn't listen error spontaniously

I have been running Postgres 9.3 continuously for days on a windows 7 machine with no problems. This morning I came in to see that Postgres, and every application using my postgres database had ...
4
votes
1answer
113 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
0
votes
1answer
21 views

Postgres: get total number of matched records using UNION

I'm running a paginated SELECT ... UNION query, and I need to determine the number of pages that are in the resultset. For example, consider the query: (SELECT id, 'table1' AS type, COUNT(*) OVER() ...
1
vote
1answer
32 views

Update by iterating table in batches faster than whole table in PostgreSQL

I have a table with approximately one million rows. It is being used in production and I ran an UPDATE which covers ~95% of those rows. After five hours I cancelled the request because it was taking ...
3
votes
1answer
56 views

CREATE DATABASE … WITH TEMPLATE seems to lose relations in PostgreSQL

I'm trying to Create a copy of a database in postgresql per the SO answer from 2009, but running into problems. In Postgres 9.3.9, this creates a database with no relations (the gcis db exists and ...
0
votes
1answer
37 views

connect to psql and run SQL command from command line

I'm trying to write a quick line in bash that will use psql to connect to a database and delete all entries in two tables: \connect some_db DELETE from some_table DELETE from another_table I'd like ...
1
vote
1answer
31 views

How to view tuples changed in a PostgreSQL transaction?

BEGIN; INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c'); -- SELECT * FROM pg_stat_activity WHERE ... -- shows records with a flag if they have been added/deleted/updated END; Is ...
1
vote
1answer
44 views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION ...
0
votes
1answer
30 views

Why are my Postgresql sessions writing directly on disk?

I have a write-heavy application on a PostgreSQL 9.4 on CentOS 6 that I'm trying to tune. Using iotop, I see that my session processers are doing all the heavy writing on disk when (to my ...
0
votes
1answer
60 views

How to combine concatenate operator on JSON values with AS

I'm trying to combine multiple JSON objects into one big object in Postgres. I could do this with an extra SELECT row_to_json (as in my first example), but this way it creates a extra object, which ...
0
votes
0answers
12 views

Is there anyway to check what is the PostgreSQL data directory programatically?

I'm doing an installer to my program and I'd like to check if the databases already exists in the computer. I do this in a horrible way, calling psql -lqt | findstr dbname. If someone knows a better ...
0
votes
0answers
19 views

Postgresql Disk Allocation in Begin End Transaction

I have a loop in transaction block that creates a temporary table,updates,exports to txt file and drops table at the end of the block. After some time I noticed that disk size was getting smaller each ...
1
vote
0answers
26 views

Grouping and Transposing Rows to Columns in PostgreSQL

I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of ...
2
votes
1answer
61 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
1
vote
2answers
67 views

Postgresql - Error in function that returns table

I just defined this function to return all column names from a given table: create or replace function GET_COLUMNS(in tbl_name character varying(30)) returns table(colunas character varying) as $$ ...
4
votes
0answers
46 views

Use case for hstore

This is the first time (I think) I have the chance to use the hstore data type, but I would like to hear from more experienced folks if what I have in mind is actually a good idea. Now, there's this ...
0
votes
1answer
34 views

How to add a check constraint in Postgres without locking the table?

I would like to add a check constraint to a very large table. Something like: ALTER TABLE accounts ADD CONSTRAINT "nonnegative_balance" CHECK balance >= 0; Unfortunately Postgres (9.3, at least) ...
0
votes
0answers
20 views

Is there something like Query Analyzer for Postgres?

We are running EF6 and I'm trying to find out exactly what query is running against our Postgres database. With Sql Server, I would just fire up Query Analyzer and look at the query there. Is ...
1
vote
1answer
34 views

Multiple inserts across tables with serial primary keys

I asked this question on stack overflow, but I'm thinking it's better on this stack. Apologies if I've got that wrong. The motivation for this question is to prepare some sql scripts I can run during ...
0
votes
1answer
30 views

Removing duplicate rows and changing related foreign keys in PostgreSQL

I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row. How do I get a list of foreign keys related to my ...
1
vote
1answer
77 views

How to drop all of my functions in postgres in single step

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. Here is my steps. 1. Use these ...
0
votes
0answers
12 views

trying to find closest road segment to a node I have

I am trying to find the closest road segment to a point that I have and return an attribute from the closest segment to the given points. I know in POSTGIS I need to use ST_ClosestPoint, St_distance ...
-1
votes
1answer
12 views

Exporting Postgres table to formatted document

Is there a way to export a PSQL table into a nicely formatted document (something that looks like a manual)? For example, if I had "Chapter", "Description" and "Date Modified" in a table, how could I ...
0
votes
1answer
23 views

Modify table constraint in PostgreSQL

Say I have a constraint ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); that needs to be modified so that zipcode length is 6. Do I have the drop the constraint ...
0
votes
0answers
40 views

Bucardo Postgresql replication master-master windows

Is it possible to use Bucardo installed on linux PC to replicate master-master postgresql 9.4 for two windows servers.
0
votes
0answers
23 views

Postgresql hot standby what happens when Master is down

Beginner question. When Master is down hot-standby become automatic master? When old master is up what happened to hot-standby and meanwhile data insert in hot-standby how is transfer to the ...
3
votes
1answer
121 views

Bad performance with select on view if check for 'is null'

I have two quite simple tables, and a view to join them on bigint fields: CREATE TABLE foo.ao ( id serial NOT NULL, ao_id bigint NOT NULL, ao_plz text, ao_community text, ao_street text, ...
1
vote
2answers
64 views

would it be possible in postgres to get a count of sql hits on a select statement

Say I have the following table in Postgres 9.4: items id | name | tag_ids 1 tire -1--2- 2 wheel -1--3- 3 transmisson -3- I'd ...
3
votes
1answer
117 views

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
0
votes
1answer
46 views

How to skip/ignore duplicate records on insert based on a generated string

I have hundreds of millions of rows in one table (snapshots_old) and I want to copy it to another table (snapshots), with one new column (snapshot_id) for which values will be generated while ...
1
vote
2answers
46 views

Displaying a value associated with a maximum value

I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product ...
0
votes
0answers
67 views

Alternative to CASE

What alternatives are there to replace the use of CASE, and will also improve the speed of the query. SELECT created_at, COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk, COUNT((CASE WHEN ...
0
votes
1answer
22 views

Can I create a unique constraint across schema in Postgres?

I have a task that I am trying to accomplish where the email address is unique. The email address is stored in the a3.user table as user_name. CREATE UNIQUE INDEX ix_user_user_name ON a3."user" ...