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
1answer
43 views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
0
votes
1answer
27 views

Get Current Row and Next Row [closed]

I want to design a query which gets the row of where clause and the next row in the table of the where clause. I am looking for something like select column a, next row in table from table where ...
0
votes
0answers
33 views

Postgres: Inconsistent DB transaction snapshot [closed]

There are 2 tables - tbl_1, tbl_2 and 2 stored procedures - sp_1, sp_2. sp_1 is used to insert a record into tbl_1. sp_2 is used to insert a record into tbl_2. sp2 will also look for data in tbl_1 to ...
0
votes
0answers
29 views

PostgreSQL queries slower than before?

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with ...
0
votes
0answers
32 views

pgAgent Job's “last result” is continuously “running”

I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content ...
0
votes
0answers
24 views

Calculate Quantity On Hand By Location

How can I calculate quantity on hand by location and validate the proposed location quantities against inventory transaction? Currently, I do not track location. I calculate total QoH via the ...
0
votes
1answer
18 views

PostgreSQL import XML to empty database, automatically create columns

The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm ...
0
votes
0answers
25 views

Looking for a recipe to merge multiple rows to single row

I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it. I need to merge the ...
0
votes
0answers
24 views

How to enable implicit casts in PostgreSQL >=9.2?

Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the ...
0
votes
1answer
21 views

Drop/Create database and create schemas all in the same script at PostgreSQL it's possible?

I'm working on a project where all the time I need to repeat this tasks: Drop database sis_db Create database sis_db Create database schemas by running a custom SQL file which contains this code: ...
0
votes
1answer
32 views

Database Security - Prevent column writing?

I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products. Am I able to prevent writing / editing of that column ...
0
votes
0answers
23 views

How to CAST a value in PostgreSQL for use in WHERE with LIKE statement? [migrated]

I'm trying to fix a SQL query for later convert it to Doctrine2 DQL since it's part of a Symfony2 project. This is what my DDL has: CREATE TABLE "nomencladores"."norma" ( "id" int4 NOT NULL, ...
0
votes
1answer
27 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
0
votes
0answers
6 views

missing pgpool-walrecrunning.so in PostgreSQL-9.4-pgpool2

I am new to PostgreSQL and pgpool concept. I have installed PostgreSQL-9.4 in master/slave setup(in pc1/pc2), and replication setup is successful. Setup of pooling is failing, I am following this ...
0
votes
0answers
17 views

Does Postgres shutdown gracefully when I restart my Mac?

When I restart my Mac OS X computer, I assume the daemon controlling Postgres (the "Postmaster" as I recall) is notified by the OS and performs a graceful shutdown of any running databases with ...
0
votes
1answer
45 views

Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I have a PostgreSQL db that was running on my server. I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't ...
0
votes
0answers
39 views

pgpool-II Master Slave mode / Load Balance Mode / Watchdog On result many primary servers

I installed pgpool-II in Centos6.5, Master Slave mode / Load Balance Mode / Watchdog On but three postgresql servers are all primary server, shows as below: IP Address Port Status Weight ...
0
votes
0answers
67 views

Best way to handle multiple query keys

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...
0
votes
1answer
16 views

sorting by partial key name in json column

I have column document with data type json. I save here data for logging. So document has: {blocks: { participant-information_primary__Primary__39011__2288960: first_name: Firsst name, ...
0
votes
1answer
20 views

For each record, count records dated earlier in PostgreSQL

I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row. So that the output would look like: col1 | col2 | ...
0
votes
0answers
21 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
0
votes
0answers
19 views

multipley queries in php for postgres [migrated]

Hello i have try to get from html file the points an make to sql queries to find the gid from the roads where nearly and in finaly query i find the best routing with dikjstra algorithm with the gid ...
0
votes
1answer
34 views

Postgres automatic failover, multiple clients to multiple masters

I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website. The majority of implementations that I have found require a single point of failure with PgPool ...
0
votes
0answers
16 views

PostgreSQL buffer page size limitations - rebuild with larger size?

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error. This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
0
votes
1answer
21 views

Multiple queries and a report

im fairly new to databases. I have multiple query's that i normally run individually at the end of loading data to my table doing simple integrity checks like length ,char types and so on (see ...
0
votes
0answers
23 views

How to store multiple raster files in postgres / postgis efficiently

This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management. I have a Postgresql server with Postgis extension running ...
0
votes
0answers
23 views

database design - upvotes for various types

In the early stages of an application I'm developing, I had two relatively simple tables - one for content and one for upvotes. The content table's schema is as roughly: data | text id | integer ...
0
votes
1answer
22 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 ...
0
votes
0answers
32 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
50 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, ...
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
77 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
20 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
31 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
43 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
1answer
18 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
0answers
59 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
23 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: ...
0
votes
0answers
29 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
0answers
51 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") ...
0
votes
0answers
22 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
46 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) ); ...
0
votes
0answers
34 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 ...
0
votes
2answers
46 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
0
votes
0answers
18 views

What is the recommended pgpool mode?

I'm trying to determine which pgpool mode to use (http://www.pgpool.net/docs/latest/pgpool-en.html#config). In order of importance my requirements are: All nodes accessible via shared public ...
0
votes
0answers
40 views

Importing CSV into multiple tables using PostgreSQL

I have a very big CSV file with the following format: TAG,TIME,VALUE as an example row: footag,2014-06-25 08:00:00.0,3400.0 I used to import it easily inside PostgreSQL 9.3 using the following: ...
0
votes
0answers
20 views

when install pgpool is also needed install hearbeat and watchdog

I am trying to get the PostgreSQL services High Availability (HA) and then I am using pgpool-II. When I install and configure pgpool, the WATCHDOG section seems to doesn't works, then I have a ...
0
votes
0answers
16 views

PgBouncer and Application Connection Pooling

I understand that PgBouncer is a dedicated component to handle connection pooling and my application would connect to PG server through PgBoucer. The connection pooling on PgBouncer side is strongly ...