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)

-2
votes
0answers
10 views

I want to move the listbox one to another listbox and update the particular listbox value [migrated]

Here, Below i used the code. I am new in VBA. I am trying to move the one list box to another listbox, but I am not able to update the database table. I am struggle on few days. Why this error came ...
0
votes
0answers
30 views

Compare two databases

I want to compare two PostgreSQL database dumps. The output of pg_dump is not sorted. This makes tools like diff useless. Background: We update the way we set up a custom database. After our ...
1
vote
1answer
40 views

Pattern matching in PostgreSQL? [closed]

I have a string in which need to extract the date, Example: S01_XXX001_000_min_01_20150526T123440583.arc , S01_XXX001_000_20150526T123440583_min_01.arc based on the pattern like 'Numeric T ...
1
vote
0answers
26 views

Copying Python data types to Postgres [migrated]

I feel like this is a very basic question, but I cannot find a comprehensive answer on this. I am wondering what is best practice for copying python data types, like lists or dictionaries, to a ...
1
vote
1answer
34 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
1
vote
1answer
38 views

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm ...
1
vote
1answer
45 views

To find data in Multiple tables and delete it?

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
2
votes
1answer
47 views

postgresql - how and why indexes are bigger than their tables

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables. Sample output: database_name | database_size | table_name ...
0
votes
0answers
10 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
0
votes
1answer
26 views

Backup-strategy for redundant data in postgresql-db.

I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These ...
1
vote
1answer
48 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
2
votes
2answers
45 views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
5
votes
0answers
40 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
2
votes
3answers
49 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
0
votes
2answers
46 views

Approach to take regarding a field that is frequently re-computed

I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text. Now, the way in which this text is processed is expected ...
0
votes
0answers
25 views

Replicating table level PostgreSQL 64bit windows

I need to replicate some tables on PostgreSQL 64bit for Windows (master -> slaves replication model) and I can't find any tool that can achieve that. Slony-I work very well for PostgreSQL 32bit for ...
2
votes
1answer
38 views

How do I prevent my query from needlessly generating rows it'll throw away later?

I have the following query: SELECT stream_points.time AS point_time, stream_points.value AS point_value, stream_predictions.value AS prediction_value, ...
2
votes
1answer
23 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 ...
0
votes
0answers
23 views

What does `@@` mean in a PostgresSQL query? [migrated]

What does the "at symbol" mean in a PostgreSQL query? For instance, I'm looking at a query where a WHERE condition is: WHERE (to_tsvector('german', nodes.name) @@ plainto_tsquery('german', 'münd')) ...
-1
votes
1answer
29 views

How to setup path to access psql from command line in Mac [closed]

psql does not work even path was set .bash_profile. Path was set export PATH=/Library/PostgreSQL/9.4/bin:$PATH in .bash_profile.
0
votes
1answer
31 views

Postgres lose xlog

I have 2 instances master-salve postgres run on AWS, the xlog folder is on ephemeral disk. The master instance was down, so I lose the xlog folder, Now, i'm trying to start up the master again and ...
3
votes
3answers
36 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
0
votes
1answer
23 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 ...
0
votes
0answers
29 views

User with Read Only access on a particular schema- PostgreSQL

I wanted to know if I can make a read only user usr1 on schema sch1 in database db1. In addition, I want that this user not to have any access to anything else in this database (not even read access ...
0
votes
0answers
45 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
1
vote
1answer
35 views

Count number of duplicate rows, sort from most to least (statistic) [closed]

I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate. I've ...
1
vote
1answer
78 views

Syntax error using preparedstatements with postgresql

I have a Java application connecting to a PostgreSQL database. Using straightforward code like: ps = con.prepareStatement(sql); if (dataTypes != null && dataTypes.size() ...
1
vote
0answers
45 views

compound index not being used on simple query

I'm running this query, and it seems to be doing a table scan , rather than using the relevant index.. select count(*) from pages where charity_id=447 and provider_id=1 this is the explain: ...
0
votes
0answers
12 views

PostgreSQL 9.3 - Move table to tablespace - Slow?

I need a help with moving table to a different tablespace. I have a really large table that is not queried much often so I've decided to move it from tablespace on SSD to tablespace on HDD and save ...
1
vote
0answers
28 views

PostgreSQL GUI Tools with Git repository integration [closed]

I'm currently evaluating PostgreSQL GUI tools that have following capabilities: Query Editor GIT Repository Integration for version control Change script generation for deployment between the GIT ...
1
vote
1answer
24 views

How to model state change timeline

I've got an app where users can place orders for recurring delivery of a particular service. The orders table has columns service_id and state, where service_id links to the service the user has ...
1
vote
1answer
58 views

Why isn't Postgres doing an index-only scan?

I am using Postgres 9.4. This is my table: id | integer | not null default nextval('frontend_prescription_id_seq'::regclass) presentation_code | character varying(15) ...
1
vote
1answer
38 views

generate new column from another table column, treating nulls are unique

I have a table that looks like the following: |device_id | login_id | ----------------------- | 1 | NULL | | 2 | A | | 3 | A | | 4 ...
2
votes
1answer
63 views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE ...
1
vote
1answer
26 views

Moving postresql 9.1 database - live

This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me. I have googled this point quite a bit, but I want to be very sure as to ...
2
votes
2answers
28 views

Setting shared_buffers in postgresql.conf does not seem to take effect

We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.) In postgresql.conf, we have: shared_buffers = 4096MB kernel shm values are set nice and high: [root@green ...
0
votes
1answer
34 views

RDS Postgres Peformance

I have recently set up a new RDS instance to test out working with a AWS postgres instance vs a heroku postgres instance. What I've found is that when I load an instance of 1000 entries in RDS its ...
0
votes
2answers
47 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
0
votes
1answer
62 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. ...
0
votes
0answers
43 views

How to check/know the highest run queries

I would like to gather statistics on the highest run queries in my database. For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
-2
votes
1answer
52 views

Permission Denied on a database created by the same user

I run this command: psql --username=openerp --dbname=sf_template_20150608111121 --no-password \ --command="update pg_database set datallowconn = false \ where datname = ...
0
votes
1answer
25 views

Postgres DB user permission

I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in PostGres. When I select ...
1
vote
1answer
22 views

Using the value of a JSON object that is stored in a PostgreSQL JSON array

I have a table that looks like this (simplified): Jobs ------------------------------- ID | Details ------------------------------- 1 | { 'tasks': [] } 2 | { 'tasks': [{'id': '1', 'due_on': ...
0
votes
1answer
37 views

the cache hit rate using pg_statio_user_tables

I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure ...
2
votes
3answers
101 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
1
vote
0answers
28 views

BDR Replication issue

I am trying to setup 2-node BDR replication and i am getting the following error while running bdr_init_copy. /usr/pgsql-9.4/bin/bdr_init_copy -d "host=10.10.145.111 dbname=apimgtdb port=5432" ...
0
votes
1answer
29 views

Cannot rename database (the user in --username is the owner)

I connect with psql and list my databases: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...
0
votes
1answer
23 views

Postgres - returning custom values ie. “true” rather than “t”

I am wondering if there is a way to show custom "values" in the output of a query purely for readability, similar to the AS statement for renaming column labels. Seems unlikely but would be useful ...
0
votes
0answers
28 views

Check if key exists in a JSON with PL/pgSQL? [migrated]

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL function. Here is the function. CREATE FUNCTION sp_update_user(user_info json) RETURNS json LANGUAGE plpgsql AS ...
1
vote
1answer
34 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...