Tagged Questions

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
2answers
30 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
17 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
7 views

Error when reorganizing the autonumber in Postgresql [closed]

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
20 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
60 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
22 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 ...
1
vote
3answers
28 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
49 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
28 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
14 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
89 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 ...
0
votes
0answers
18 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
25 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
72 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
38 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
3answers
66 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
17 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
33 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
47 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
33 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
32 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
22 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 ...
1
vote
0answers
43 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
0
votes
1answer
23 views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine. create or replace function uuid() returns uuid as ...
2
votes
2answers
266 views

Updating every cell of of a table?

I have a table with m columns and n rows. The ETL tools I was using wrote an empty ('') to every cell without a value and I would like them to be NULL instead. This got me thinking if it would be ...
1
vote
1answer
31 views

Change existing column in PG to auto-incremental primary key

I have a database in Postgresql, which was migrated from SQL Server (only data). On SQL Server, a table from this database has these columns: measure_id datum measure where measure_id is ...
2
votes
1answer
41 views

(How) can I return the most specific row on a join?

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
1
vote
1answer
50 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
1answer
36 views

Getting rid of partitioning in Postgresql 9

it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer. What's the ...
0
votes
1answer
47 views

Use if exists in postgresql [closed]

I know about the exists feature, but that does not help me with the given situation. What I want is: Check if some row exists, and if exists then check for an another row if row exists. Here's ...
-1
votes
0answers
15 views

pg upgrade saving database definition taking time

I am in process of a pg_upgrade from 8.4 to 9.3. I am using this technique: http://momjian.us/main/writings/pgsql/pg_upgrade.pdf The upgrade has been running since 250 hours, and it has been on the ...
2
votes
1answer
31 views

Postgresql: How do full_page_writes help prevent data loss?

From the postgresql docs [full page writes are] needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that ...
0
votes
0answers
14 views

PGBouncer configuration

Let's assume that, for a Django website, I have a PostgreSQL server with only one database in it. max_connections is set to 200. Apart from serving the website, I run each day a local process that ...
0
votes
0answers
19 views

PostgreSQL SELECT and JOIN question [duplicate]

Trying to fix the previous post text formatting issue there was and I did not recognize: I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( ...
0
votes
0answers
42 views

How to deal with finding nearby locations when given a zip code or city?

I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and ...
0
votes
0answers
26 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
1
vote
1answer
39 views

joins with “could be blank” conditions

Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off). I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
2
votes
1answer
136 views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
0
votes
0answers
30 views

postgres: access to schema objects within a function

Here's my problem: I have Schema A, owned by User 1, that owns all objects. I've created schema B, owned by User 2. I have granted User 2 all privileges to the objects owned by User 1. When logged ...
1
vote
1answer
16 views

Removing a Key From HSTORE in Postgresql

At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According ...
0
votes
1answer
36 views

When is the postgres auto vacuum executed

I am using an older version of postgres(8.4.20). I know that the auto vaccum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
1
vote
3answers
67 views

Longest matching suffix while using aggregate functions

Background Looking to find the longest matching string suffix. Setup Consider the following fiddle: CREATE TABLE noun ("label" varchar(10)) ; INSERT INTO noun ("label") VALUES ...
1
vote
1answer
41 views

Tables names in a supertype/subtype schema

I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
1
vote
2answers
38 views

SET ROLE via parameterized query

In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I ...
1
vote
1answer
36 views

Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...