PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
1answer
9 views

“Wrong affix file formate for flag” while loading dictionaries from LibreOffice in PostgreSQL 9.3

Trying to get good TEXT SEARCH results out of PostgreSQL in English, German and Dutch made that I downloaded proofing tools of LibreOffice (tried of version 4.3 and 3.3) and iSpell. I placed the files ...
1
vote
2answers
762 views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
0
votes
0answers
25 views

Optimizing query - understanding explain

I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" ...
0
votes
1answer
442 views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
0
votes
1answer
12 views

pg_get_viewdef error while running pg_dump in PostgreSQL

Occasionally, I get the following error while doing a simple pg_dump ( pg_dump -U postgres -Fc -f mybackup.dmp mydb): pg_dump: [archiver (db)] query failed: ERROR: could not open relation with OID ...
0
votes
3answers
61 views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
0answers
11 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
0
votes
0answers
21 views

“root” execution of the PostgreSQL server is not permitted

I run this code as root: /usr/local/bin/postgres -D /usr/local/pgsql/data because it wanted me to run it. However it gives this error; "root" execution of the PostgreSQL server is not permitted. ...
0
votes
0answers
24 views

Where to download (Dutch) Postgresql Fulltext Search dictionaries

PostgreSQL enables me to configure what dictionaries I would like to use when preforming a fulltext search. The following types of dictionaries can be set: stop ispell simple snowball synonym ...
2
votes
2answers
27 views

Fuzzy Matching with Postgresql 9.3

Right now I am using ts_rank (with ts_vector and ts_query) to search strings for relevance. While this is doing a great job for me so far. I was hoping to work in a bit of fuzzieness into the ...
0
votes
2answers
20 views

How to simulate postgresql recovery mode?

I would like to simulate putting postgresql into "recovery mode". Few times i have situation where postgresql puted himself into recovery mode. In logs i had: Oct 18 06:37:35 xx postgres[5738]: ...
0
votes
0answers
26 views

Insert of select id on multiple tables

When importing some data (10000) rows each time, I need to perform an INSERT or SELECT (insert if doesn't exist or select if exist and return table_id) 7 times for each row. At the moment I use the ...
1
vote
1answer
44 views

Is there a faster way to get OR behavior in Postgres?

Is there a faster way to do an inner join with an OR clause. I have a query that looks something like this: INNER JOIN (select u_id, b_id from t1) AS t2 ON t2.u_id = t1.u_id OR (t2.b_id = ...
1
vote
1answer
25 views

Slow postgresql query with pagination and filtering

For the DB in question, I have followed the advice of Pagination Done the PostgreSQL Way That is, results are being paginated on a composite index with a definite sort order. Now, my problem is ...
17
votes
6answers
11k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
0
votes
2answers
97 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
18
votes
4answers
6k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
2
votes
1answer
27 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
0
votes
1answer
71 views

mongodb on-premise: will it work?

We are planning to package MongoDB-PostgreSQL hybrid solution in our application that will be deployed on customer site on-premise. It will be packaged as a software virtual appliance. Our reason for ...
3
votes
1answer
66 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
0
votes
1answer
16 views

Postgres blocking UPDATE on new column?

For the life of me, I can't figure out why Postgres won't let me issue this update statement below. Assume any table called dim_dates where there's a column dateType: ALTER TABLE dim_dates DROP ...
-1
votes
0answers
19 views

PostgreSQL how to change to Repeatable read [on hold]

I'm running a postgreSQL 9.3 database and I have stored procedures in it. I access the database using JDBC with Java calling the stored procedures with prepared statements. I know that the default ...
0
votes
1answer
14 views

Postgresql two tablespaces one on SSD and one on SATA III HD

Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two ...
0
votes
1answer
13 views

postgres removing user from role membership

How do I drop a role member? My role name tester is member of role postgres (which I do not want). How do I remove this membership? Role name | Attributes | ...
0
votes
1answer
223 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
-3
votes
0answers
29 views

How to handle big table? [closed]

How to handle big data in Postgres without the partition function? As we all know that Postgres does not support partition very well. There is a big table which has billion rows and total size about ...
0
votes
1answer
79 views

Why can't modern databases have unlimited columns [closed]

I am just curious as to why big open source and commercial RDBMSs can't, don't or won't implement unlimited columns per table? Is there a technical or business reason why this can't be done. MongoDB ...
0
votes
2answers
18 views

Postgres inherit indexes for partitioned tables

I have a table with approximately 60 million rows that I have partitioned by state into 53 sub-tables. These tables "inherit" the big table like so: CREATE TABLE b2b_ak (LIKE b2b including indexes, ...
0
votes
1answer
23 views

'Prioritize' one column over another

I'm working with subscription table currently and I'm creating a cron job that will take records based on some condition from the subscription table. I want to select all subscription records that ...
2
votes
1answer
70 views

PostgreSQL return second 'Group' as an array

I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this... websites ------- website_id (pk) ...
1
vote
2answers
28 views

Postgres top-k with ties

Assume I have the following DB table with 3 integer fields. A | B | C 1 | 2 | 3 1 | 2 | 4 1 | 3 | 1 2 | 4 | 2 2 | 4 | 3 When I do: SELECT * FROM dbTable ORDER BY A,B LIMIT 1; I get 1 | 2 | 3 ...
1
vote
1answer
55 views

How to get back the PostgreSQL database to a specific poin in time?

I am running a batch script(10:40) in postgresql database which will insert values in differnt tables on postgresql till (11:00).Later i came to know that the script was executed mistakenly.Now how ...
0
votes
1answer
64 views

PostgreSQL replication with fsync disabled?

I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL. However, as part of my scheme to meet another ...
0
votes
1answer
28 views

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. ...
0
votes
1answer
335 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
0answers
6 views

Error when reorganizing the autonumber in Postgresql [on hold]

When the Autonumber property is changed from False to True and Postgresql is the DBMS to be reorganized, it fails with an error like : 'table does not exist'.How can I solve this?
-1
votes
0answers
24 views

Find the sid of suppliers who supply every red part [closed]

Suppliers ( sid: integer, sname: string, address: string, country: string ) Parts ( pid: integer, pname: string, color: string, weight: real ) Catalog ( sid: integer, pid: ...
0
votes
1answer
16 views

PostgreSQL Give idetifier for transaction?

Is there a way to give a identifier for Postgresql transaction. Ex : begin transaction a; ... If not, will this be supported in future. I'm asking this if transaction has identifiers then true ...
1
vote
1answer
419 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
0
votes
1answer
17 views

postgresql replace table without losing dependancies

i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the ...
1
vote
1answer
45 views

Writing a view that caches to another table

I have a table market_trades with columns timestamp, market_id, and amount: api_production=# \d market_trades; Table "public.market_trades" Column | ...
2
votes
1answer
275 views

Using PgBarman for PostgreSQL 9.3 backup

I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes: take db backups every day/week/month use a Synology NAS as remote backup device I'm thinking of using Postgres ...
0
votes
0answers
18 views

NodeJS, Sequelize and a local PostgreSQL database

We're working with PostgreSQL, Sequelize JS and Node for quite some time now. We've created a way of creating tables with Sequelize, so it's more bug proof than editing the database structure directly ...
0
votes
1answer
46 views

Query Processing at backend (I/O on disk)

I have a table with 100k rows created on PostgreSQL 9.3 create table demo_bbb ( id numeric NOT NULL, code_bbb character varying, column_02 character varying, column_03 character varying, ...
0
votes
1answer
20 views

postgresql updating integer column taking more space than expected

I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8)); My query looks like, alter table B add column b1 integer; update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = ...
0
votes
1answer
25 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
-1
votes
0answers
31 views

Display all records from a table

You can see my schema here. There are few things that I want to get but I don't know how to. In the brand_product table I have all my products associated with their brands, but as you can see in ...
0
votes
1answer
22 views

Have multiple foreign keys or one

I have 3 table: brand: brand_id(pk), name product: product_id(pk), name brand_product: brand_product_id(pk), brand_id, product_id And I have a report table, at the moment it looks like this: ...
27
votes
2answers
30k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
2
votes
0answers
23 views

Postgres and numeric json arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ...