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
41 views

Insert performance

I am having trouble with Posgresql insert performance. There are 2 tables, documents and words, where documents is essentially a mapping from an external path to a surrogate id, which is foreign ...
0
votes
1answer
20 views

Is it possible to set order in which inherited tables a scanned in postgresql?

I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125. When I run explain ...
3
votes
2answers
67 views

Understanding a PostgreSQL join [closed]

I have seen the following query in the PostgreSQL documentation. Unfortunately I don't understand the join criteria. create temp table tmp_stat_user_tables as select * from pg_stat_user_tables; ...
1
vote
1answer
34 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 ...
67
votes
4answers
103k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
1
vote
0answers
31 views

Three Vital Postgres Columns Corrupted

I run a small website which uses a Postgres database. There are regular back ups based on the amount of traffic we have every few days but there was a recent surge of activity just before a power cut ...
2
votes
1answer
3k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
1
vote
0answers
18 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
1
vote
1answer
26 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 ...
0
votes
3answers
69 views

Boolean check on fields returning any of them except if all have same value

Assuming we have 4 boolean fields a,b,c,d How can we return any of the fields that are true, but not nothing when all 4 are true? where a=true or b=true or c=true or d=true will return a row where ...
1
vote
1answer
44 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 ...
2
votes
1answer
93 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
1
vote
1answer
48 views

Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?

I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more ...
-1
votes
0answers
20 views

How to fill data of PostgreSQL into combobox using PHP [migrated]

I want to fetch data from PostgreSQL into my popup box. In the popup window, two comboboxes are there and on these comboboxes I want to fetch data from SQL into comboboxes. Here is the code of popup ...
1
vote
1answer
911 views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
2
votes
1answer
27 views

Return top 100 words from strings

Not sure this is even possible but I was wondering if there is any way of using PostgreSQL to return the top 100 most occurring words from a database column containing strings of varying lengths? The ...
3
votes
2answers
43 views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
0
votes
3answers
48 views

PostgreSQL - Simple column binding

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk. My SELECT is of the form: SELECT t1.id, t1.value, t2.value, ...
0
votes
1answer
21 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
1
vote
2answers
48 views

Optimising a query to get rows where x is between pairs of values from another table

PostgreSQL 9.4.5 In my database there are 2 tables, vcfentries and exons. I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the ...
5
votes
1answer
63 views

How to improve this union to ensure consistent results?

I've been trying to find an reliable and economic way of counting rows over a 7 day period and so far, this is the best I've been able to come up with: Schema CREATE TABLE IF NOT EXISTS "data" ( ...
-1
votes
1answer
18 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
1
vote
2answers
36 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
396 views

Replication Lag on Postgres AWS RDS Service

We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
3
votes
1answer
47 views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
3
votes
1answer
40 views

What are the limits of Postgresql's large object facility?

I've not found an equivalent of the about PostgreSQL page that covers the limitations of the large object facility in particular, but reading between the lines I think the following is true: No more ...
10
votes
3answers
8k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of Would ...
0
votes
0answers
14 views

Query Postgres table by Block Range Index (BRIN) identifier directly

I have N client machines. I want to load each of machine with distinct partition of BRIN index. That requires to: create BRIN with predefined number of partitions - equal to number of client ...
2
votes
1answer
62 views

PostgreSQL 9.4 query seems very slow

I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
-1
votes
0answers
23 views

Change date OUTPUT to DMY format permanently on Postgres [duplicate]

Is there a way to change permanently the date output format on postgres to DMY? I have changed datestyle setting in postgresql.conf which will allow me to insert dates as DMY format but when I query I ...
1
vote
2answers
43 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 ...
0
votes
0answers
14 views

Deadlock on Postgres 9.3 Sharelock executing same function at same time

Postgres 9.3 Debian 7 I'm developing a gambling website (in alfa now) where you bet credits (I call coins) in card games. There's an unique pg function to add/subtract user coins that updates a ...
4
votes
3answers
424 views

Avoid double query on the same table

I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table. Query 1: SELECT ...
4
votes
1answer
65 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
2
votes
2answers
32 views

In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose ...
0
votes
0answers
15 views

Difference between execution times [duplicate]

I managed to make a more efficient my query disabling the sequential scan, but the time in the explain analyze is really different from the real execution of the query, "Execution time: 4854.186 ms" ...
0
votes
0answers
41 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
1
vote
1answer
45 views

How to use SSL/TLS between server and client with Postgres?

I don't know much with it comes to security. I'm deploying a virtual machine with my database in a cloud service. I would like to encrypt all the communication between this server and any clients. How ...
1
vote
1answer
20 views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
2
votes
1answer
37 views

Is there some kind of table-level replication in PostgreSQL?

I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from ...
2
votes
2answers
177 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
34
votes
2answers
22k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
2
votes
2answers
70 views

PostgreSQL Server: Many-to-One replication / Transactional Replication

I have several PostgreSQL DBs in different geographical locations (local sites). Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, ...
2
votes
2answers
66 views

Retrieving changed rows only from a table with 10^7 rows

I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well. I've ...
1
vote
2answers
43 views

SELECT…FOR UPDATE - Will only an UPDATE release the lock?

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT. If I execute a rs.close(); or pStmt.close(); without ...
3
votes
1answer
28 views

How to paste chinese characters in psql terminal?

I am trying to paste chinese characters into psql terminal: $ psql => \encoding => UTF8 # Ctrl+v (艺术与设计) => ?????设计 When pasting into normal terminal (Max OS) it works fine: $ # Ctrl+v ...
2
votes
1answer
24 views

Postgres soft delete partition, what to do with Foreign keys?

I have a table A that has a couple of other tables (B, C) attached to it (many rows in B and C for one row in A). I'm having performance problems with A, so I thought I would partition it using a ...
2
votes
1answer
296 views

Postgres query plans and tuning parameters

I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
0
votes
0answers
13 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the ...
1
vote
0answers
26 views

What controls the precision when writing reals in postgres copy command?

I do the following copy command from psql: copy yy_caa_epdall to '/tmp/CAAImport-1234567' delimiter '|' csv header; and get a file with lines like the following: ...