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

learn more… | top users | synonyms (1)

1
vote
1answer
68 views

POSTGRES DB Server Blunder

This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive. Is it possible to recover ...
1
vote
1answer
887 views

Postgresql querying trends

Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches So I have a database which records motor races, with the ...
1
vote
1answer
959 views

Trying to start a newly created cluster in PostgreSQL on Windows

I just created a new cluster using PostgreSQL 8.3 and looks like everything worked. What I am having issues with is trying to start the server of this new cluster. My two clusters are on different ...
1
vote
1answer
803 views

Something wrong in pg_hba.conf

this my configuration: #postgres.conf: listen_addresses = 'localhost' #pg_hba.conf: local all postgres md5 local all appuser trust local all devuser trust local all all peer i'm connecting in ...
1
vote
1answer
108 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
1
vote
1answer
1k views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
1
vote
1answer
2k views

Postgresql nested row_to_json not working

This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
1
vote
1answer
6k views

Retrieving data from inner join using LIMIT and OFFSET

These are my two tables: table1 qid[PK] |gid[PK] |abcd | xyz | date ---------------+---------+---------+------+------------ 00001 | qwe | 54 | a | 1994-11-29 00002 ...
1
vote
1answer
1k views

PostgreSQL 9.1 - Asynchroneous commits performance boost

During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
1
vote
2answers
179 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
1
vote
1answer
5k views

Pgpool install - libpq is not installed or libpq is old

Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error: configure: error: libpq is not installed or libpq is old ...
1
vote
2answers
235 views

PostgreSQL replication and large import

I am tasked with making large PostgreSQL imports to master instance, and then replicate it across the nodes as read-only data. Still, the CMS system has to be able to write data and replicate across ...
1
vote
2answers
2k views

Too many TIME_WAIT connections in PostgreSQL

I am using PostgreSQL 9.0 on Centos 5.7 Final. At any given there are around 1,500-2,200 connections open for PostgreSQL on port 5432 which are in TIME_WAIT state. I have a feeling this is not good. ...
1
vote
1answer
3k views

I would like to launch a pg_dump command from a remote computer

I would like to launch the command: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb from a remote computer. Both the database and the remote computer are under xp. ...
1
vote
1answer
3k views

Really slow DISTINCT ON query with multiple joins

Originally posted: http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres The songs table has only about 4k rows, posts and stations have ...
1
vote
2answers
195 views

Perf of query of large table by date?

I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like: entry_id (pk), sensor_id (fk), measurement_value, created_at I ...
1
vote
1answer
2k views

Recover PostgreSQL database data

I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?
1
vote
2answers
1k views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
1
vote
1answer
16 views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
1
vote
1answer
37 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
1
vote
1answer
58 views

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed ...
1
vote
1answer
37 views

regexp_replace() vs. replace() to replace all matches in a string

By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior ...
1
vote
1answer
32 views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
1
vote
1answer
32 views

INSERT INTO using the RETURNING values from another INSERT

I'd like to do something like this: INSERT INTO table2(name) INSERT INTO table1(name, address) VALUES ('me', 'home') RETURNING name; And then I would expect a new record in table2 with 'me' in ...
1
vote
2answers
45 views

Postgresql: how to remove a GRANT on multiple tables?

I have a big database with 40 schemas and plenty of tables inside each one. Some tables have a GRANT SELECT ON TABLE schema1.table1 TO user1grp. I no longer need user1grp group role. If I delete ...
1
vote
1answer
40 views

How to trunc double precision number without rounding?

I have '9.999999999'::double precision number and I want to convert it to decimal(8,2) without any rounding. The result I am expecting is 9.99. So far I tried: select trunc('9.999'::double ...
1
vote
1answer
29 views

Ordering in subqueries

This is my query: select locid,tlid,locdescription from ( select locid, tlid, locdescription from trip_log left join trip_log_det on tlid=tldtripid left join locations on tldlocation=locid ...
1
vote
1answer
61 views

Best way to transfer millions of rows from one database to other

There are two databases: DatabaseA and DatabaseB with the same schema. These databases have millions of rows. The plan is to convert them into one single database (DatabaseCommon) and multiple ...
1
vote
1answer
64 views

What is default behavior of PostgreSQL without specifying COLLATION?

What is default behavior of PostgreSQL, if i don't specify collation externally? One of the main questions, is it case-sensitive or not?
1
vote
1answer
70 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
1
vote
1answer
39 views

Disable/enable PostgreSQL trigger inside function for the same table

I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update ...
1
vote
1answer
51 views

Why does this PostGIS query take so long to run?

I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing ...
1
vote
2answers
50 views

Postgresql: Sortable index on array values

Suppose the following table: CREATE TABLE foo (id serial, category int, bar int[]); INSERT INTO foo VALUES (1, 1, '{4,10,20}'); INSERT INTO foo VALUES (2, 1, '{1,8,9}'); We want to query sorted ...
1
vote
1answer
82 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
1
vote
2answers
56 views

Postgres tablespace size is bigger than database size

PostgreSQL tablespace size is bigger than database size. What is the problem about that? SELECT pg_size_pretty(pg_database_size('ibidev')); pg_size_pretty ---------------- 2578 MB (1 row) SELECT ...
1
vote
1answer
161 views

Slow query - Optimize - PostgreSQL

I've got a slow query here... could you please help me to optimize it? Query: SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer, sum(revenue) AS revenue, ...
1
vote
1answer
386 views

Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error: Stopping old cluster... Disabling connections to the ...
1
vote
1answer
64 views

Improve the speed of a BETWEEN search

I have a query witch is selecting some data between two dates: SELECT * FROM rk.p_reports WHERE project_id = 1 AND engine_id = 1 AND type_id = 1 AND created_at::date BETWEEN now()::date - 14 AND ...
1
vote
1answer
33 views

how to do a count on a grouped by value in Postgres 9.4

I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid' id | fulfillment_status | ...
1
vote
1answer
40 views

How to optimize query with max on Postgres?

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It takes over an hour ...
1
vote
2answers
79 views

Select column to be inserted based on input column value

Assume the following two tables (and ignore the quality of the database design): Table 1 has columns id, sequence_number, value: it contains many rows. Table 2 has columns id, seq1, seq2, seq3, ...
1
vote
2answers
58 views

Update a table after insertion to another table in PostgreSQL?

Let's say that my database has 2 tables (Person & Car): Person (personID, number_of_cars) Car (carID, owner) where the owner attribute in Car refers to the personID in the Person table. Now, ...
1
vote
1answer
127 views

Where can I see the exact row count of a table in pgAdmin III?

Where can I see the exact row count of a table in pgAdmin III? I know where to find the approximate row count: I am also aware I can SELECT COUNT(*) FROM my_table. But I want to know whether the ...
1
vote
1answer
61 views

Understanding index fillfactor

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours). Now, ...
1
vote
1answer
71 views

PostgreSQL Streaming Replication - corruption on master

We have PostgreSQL 9.3.9 streaming replication between Master DB and Slave DB. Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both ...
1
vote
3answers
205 views

Best DB structure for large CSVs?

I have previously posted on stackoverflow, however, I have been directed here. I'm getting to the stage where I need to analyse lots of flat CSVs: Read-in around 500GB of CSVs (e.g. daily data ...
1
vote
1answer
45 views

ORDER BY text doesn't use my index?!

currently this query doesn't use a index even after I created one: EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ; ...
1
vote
1answer
46 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
1
vote
1answer
72 views

How I create backup with backup by dump and WAL files in PostgreSQL?

I have a backup and WAL files, I like update my backup with WAL files. Anyone know how to do it? Thanks
1
vote
1answer
83 views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...