PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

3
votes
2answers
58 views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
0
votes
1answer
86 views

How to import large sql file in postgres?

Trying to import sql file around 8GB using these commands sudo -u postgres psql yourdb -f my_file.sql sudo -u postgres psql yourdb < my_file.sql sudo -u postgres cat my_file.sql | psql yourdb ...
1
vote
1answer
45 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
1
vote
1answer
37 views

Size of Postgres database growing

Our Postgres database is currently at about 68G of size and growing at a rate of 6G/month. I am investigating ways how to scale the database. Would be nice if someone can give some advices as I am ...
0
votes
0answers
33 views

Combine 3 tables

I have 3 tables, they have in common row index, date, profile. These 3 tables each have partitions based on profile. I need to combine the data from those tables to produce a report at the moment I ...
0
votes
0answers
78 views

How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, ...
1
vote
2answers
62 views

Optimizing for data that does not fit in memory

We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from ...
0
votes
1answer
23 views

Insert any type of text in a column

How can I insert any type of text into a column of type text? At the moment I am using this: $$description$$ But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I ...
0
votes
2answers
23 views

Why Postgresql 9.2 word boundary not working!

In my postgresql version 9.1 this query is working fine: select id,name from flash_card_set where name ~* '\yACT\y' and matching: text ACT txt,ACT tests,tests ACT But in my postgresql version 9.2 ...
1
vote
1answer
46 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 ...
0
votes
0answers
49 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
0answers
162 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
1answer
22 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
0answers
33 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 ...
0
votes
0answers
44 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 ...
0
votes
2answers
35 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
1answer
21 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 ...
2
votes
1answer
36 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
26 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
15 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 | ...
1
vote
1answer
86 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 ...
1
vote
1answer
49 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 = ...
0
votes
1answer
25 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
2answers
53 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
1answer
127 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. ...
1
vote
2answers
35 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 ...
0
votes
1answer
19 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 ...
0
votes
1answer
36 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
77 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
0answers
90 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
28 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 = ...
1
vote
3answers
55 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
28 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 ...
0
votes
1answer
63 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
24 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: ...
2
votes
0answers
52 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 ...
1
vote
0answers
37 views

triggers vs views [closed]

I have a mysql database with the following tables Penalties Scores player_instance When penalties is updated/deleted then it will update scores (via a trigger). Also when a player-instance row is ...
1
vote
0answers
17 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 ...
1
vote
1answer
131 views

Is there a way to store a PostgreSQL database directly on a block device (not filesystem)?

I am reading about where PostgreSQL stores its data it needs to offer me its service (here: http://www.postgresql.org/docs/8.4/static/storage-file-layout.html ) As it is writen there PostgreSQL's ...
1
vote
0answers
33 views

Postgres advisory lock deadlocks itself?

I am executing pg_advisory_locks using the following set of statements: SELECT pg_advisory_lock(317,2); UPDATE posts SET dislikers = array_remove(dislikers, 7) WHERE id = 317; update posts set ...
1
vote
1answer
44 views

Left outer join not returning all columns in a grouping query

I'm trying to query all users to find the price of orders in the last month. I'm doing this on postgresql. Here's a toy database to show my problem: I have a 'people' table and a 'orders' table. I'm ...
2
votes
1answer
79 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
1answer
43 views

Linking first and second level composite primary keys

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not ...
1
vote
2answers
98 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 ...
0
votes
0answers
44 views

Bucardo doesn't do fullsync

I have a 2 computers, one of them called master, and the other called slave both using PostgreSQL 9.3.5, but has different OS (32-bit and 64-bit) I have installed and setup the bucardo on the server ...
0
votes
1answer
39 views

How to order by Levenshtein distance?

I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors. It is a table with all the books I plan to read ...
0
votes
1answer
55 views

Duplicate records on joining

I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to ...
0
votes
0answers
83 views

How to select column names that contains specific value from other table's column

Suppose I have table items and logs. Both tables have col1, col2, col3, col4... col(n). col1.. col(n) in table items simply specifies key name (col1 = "beef", col2 = "chicken", col3 = "bacon") ...
1
vote
0answers
43 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
0
votes
2answers
29 views

Best way to update the content of large lookups tables

In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify). Nightly we'd like to update this information by deleting all the ...