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)

0
votes
0answers
8 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
1answer
10 views

Array column vs another table when the relationship is quite small

The Data I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is ...
1
vote
3answers
25 views

Get seconds of day in Postgres

In Java we use Joda-Time to get the seconds of a day as an int value (despite the date): date: 10-10-2014 00:00:30 -> second 30 of day date: 11-10-2014 00:01:30 -> second 90 of day date: ...
0
votes
1answer
16 views

Postgresql single base on multiple HDD partitions

Imagine a situation: I got a Postgres database working on single server on one of HDD partitions (partition size 100GB) Database grows up to 100GB taking whole free place on the partition Database ...
0
votes
1answer
23 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
22 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
19 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 ...
0
votes
0answers
18 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
27 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 ...
-4
votes
0answers
26 views

personal database [on hold]

Normalize each of the following datasets by splitting them into multiple tables where repeating fields are placed into a separate table with a foreign key. For each dataset, show how many tables ...
0
votes
0answers
13 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
1answer
30 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
18 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
18 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
24 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
40 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
26 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
14 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
27 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
32 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
21 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
17 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
22 views

PostgreSQL how to change to Repeatable read [closed]

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 ...
2
votes
1answer
29 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
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 | ...
1
vote
1answer
33 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 ...
0
votes
1answer
80 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 ...
1
vote
1answer
46 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
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
2answers
30 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
34 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
29 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 ...
-3
votes
0answers
33 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 ...
-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 ...
-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?
0
votes
1answer
18 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
56 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
20 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
21 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
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
2answers
19 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
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 ...
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
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: ...
2
votes
0answers
26 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
35 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
12 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
87 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 ...