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)

1
vote
1answer
106 views

Replicating a postgres database for testing

I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better ...
0
votes
1answer
112 views

How the xlogs files travels form Master to Slave

I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs. If I have this configuration in Master postgresql.conf: archive_command = 'test ! -f ...
0
votes
0answers
43 views

Recursive query to break time intervals into standard-sized parts

I'd like to write a reusable function which takes 3 arguments a timestamp start_time a timestamp end_time an array of intervals interval_array such that each interval (except the first) divides the ...
0
votes
3answers
8k views

Export Postgres table on remote server to CSV file on local machine?

I have access to DB on remote server where I can do only select command, So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permission to ...
0
votes
1answer
114 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
2
votes
3answers
107 views

Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to ...
1
vote
1answer
19 views

Find the number of where statements that matched in postgresQL

Right now I have a query that looks something like this SELECT id FROM products WHERE products.details->'make' = ? OR products.details->'model' = ? OR products.details->'year' = ...
0
votes
0answers
56 views

“kind does not match” after failover

We are using pgpool-II 3.3.3 with 4 nodes of Postgres 9.3. At some point today 2 of the 4 nodes had errors and pgpool did a successful failover. When the 3rd node had errors pgpool also reported a ...
0
votes
0answers
27 views

PostgreSQL is not installing correctly

I am installing postgreSQL in windows 7 64 bit system. The problem I am facing is that the setup is not finishing properly, the window showing installation is not terminating. The icons are visible ...
0
votes
0answers
208 views

Not able to create or modify tables using Oracle SQL Developer with psql driver

So I would like to use the Oracle SQL Developer for managing my postgresql database. Connecting to that database works fine with SQL developer 4.0.3. I am using this psql driver: JDBC41 Postgresql ...
0
votes
1answer
66 views

Compare the same column on different dates

I have two tables project_key: project_id, key_id project_report: project_id, key_id, level, created_at I would like to compare the column level when created_at is now() and created_at is ...
0
votes
1answer
28 views

How to change the mount point for a column in postgresql table?

This is my scenario I have a table t with following columns id int link varchar(500) name varchar(20) sal text In the link column we will store the location for the pdf files(mount point) ...
4
votes
4answers
5k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
0
votes
0answers
44 views

Postgres - Combine similar queries with extra where clause

I am trying to combine a number of fairly time consuming queries into one (if possible) The database contains race-track data that records the times for laps completed by drivers (or users here), the ...
1
vote
2answers
46 views

How to understand “object-relational”?

I can't understand why the document say "PostgreSQL is an object-relational database management system"?Can somebody tell me the reason, or give me some article about this question. Thanks a lot. ...
3
votes
2answers
5k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
1
vote
0answers
42 views

Can we pass a varchar/text datatype in to a variable in pgbench?

--pgbench sql script [pg93@db1 load_test]$ cat get_email.sql \setrandom cnt 1 100000 \set v_username 'user_' || :cnt begin; select get_user_email(:v_username); end; get_user_email() is a function ...
1
vote
1answer
127 views

Postgres create an insert return rule

I am failing to create a rule which will return the insert's data. The table is: CREATE TABLE foo(a int, b text); My attempt for the rule is: CREATE RULE return_data AS ON INSERT TO foo DO ...
0
votes
1answer
17 views

The number of copies for each table stored in physical storage

I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for ...
4
votes
1answer
629 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
2
votes
1answer
115 views

postgres: Database replica with some schemas writable

I have two computers. Computer A is dedicated to collecting data and storing it in its local database. Computer B is for analysis. A is running flat out just with the data collection, so I want to ...
3
votes
2answers
3k views

Postgres primary key sorted in the reverse order. Will it use the index efficiently?

My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed. My list views show records in the reverse ...
0
votes
1answer
40 views

Trying to load a file into a database on virtual machine

I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was ...
0
votes
1answer
25 views

dm_exec_query_stats in PostgreSQL

I recently started using PostgreSQL after using SQL Server for a while. Is there something like dm_exec_query_stats? I haven't been able to find anything other than logging every long running query. ...
5
votes
1answer
199 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
3
votes
1answer
245 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
3
votes
1answer
30 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
4
votes
1answer
236 views

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have ...
3
votes
2answers
1k views

Trying to set up new data_directory on Postgres 9.3

Ubuntu 14.04, recently installed Postgres 9.3. Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that. However, I get ...
0
votes
2answers
125 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT ...
1
vote
0answers
63 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
161 views

pg_basebackup could not get WAL end position from server

I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
2
votes
1answer
175 views

Delay Trigger Execution

Let me explain the scenario first before asking the question. I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user ...
0
votes
1answer
43 views

using string concatenation to reference a table

For a database systems project which tracks entities histories, I stumbled acros the following problem. Assume we have the table "entity" CREATE TABLE entity ( eID SERIAL PRIMARY KEY, eName ...
1
vote
1answer
50 views

How to trigger newlines in log messages?

Is it possible for log messages to span multiple lines? I am expecting: RAISE WARNING 'Line 1\nLine 2'; to result in the following log: Line 1 Line 2 But instead I get: Line 1\nLine 2 Any ...
1
vote
1answer
903 views

Can I use pg_restore to restore data to subset of tables?

I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users ...
0
votes
1answer
88 views

Choosing a RULE instead of a TRIGGER

I my current database schema, there are hundreds of tables that reference another single table (called transaction_entry), that stores generic information about each row (when it was created, last ...
0
votes
1answer
44 views

PostgreSQL migrate data from one table to another (which inherits the first one)

I have a table "company" and another named "school" ("school" table inherits "company"). Most of the data is community generated and sometimes a cleaning is needed. I would like to be able to ...
0
votes
1answer
875 views

Escaping delimiter in postgresql

I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped: Data structure: create table v (a varchar); Data file: bus ...
1
vote
1answer
83 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
0
votes
2answers
386 views

Does Postgresql support different transaction isolation per connection?

I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other ...
0
votes
0answers
50 views

updating previous row with current value

select min(hh.id) AS id, he.name_related as name, hh.date_from as date_from, hh.date_to as date_to, hh.name as reason, sum(hh.number_of_days) as no_of_days, dy.a as remove, ...
2
votes
1answer
99 views

trigger can't access column loop

SSCCE: The following script: $ cat test.sql CREATE TABLE public.foo ( loop INTEGER ); CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$ SELECT TRUE; $$ LANGUAGE SQL; ...
1
vote
1answer
74 views

why is the name `loop` treated differently in PostgreSQL

I issue the following DDL: CREATE TABLE foo ( loopa INTEGER, loop INTEGER ); Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to ...
3
votes
4answers
771 views

Temporary schema per connection?

I am trying to migrate my unit tests from H2 to Postgresql. Currently, H2 gives me an in-memory schema such that each connection maps to a unique schema, creates the tables, runs the test, and drops ...
0
votes
2answers
83 views

Need to group by column while ignoring some text format

I have the following data. Sport -------------- Baseball Football Baseball [a] Football [a] [b] Basketball How can I group this to ignore the data in the brackets?
0
votes
0answers
17 views

produce a view from the output of a with query

I would like to produce a view based on few tables. So far I did this: WITH keyword_group AS( SELECT g.id AS group_id ,array_agg(pk.keyword_id) AS keyword_ids FROM group g ...
0
votes
1answer
37 views

Postgres get rows where a field stopps growing

I have a table which has the following structure id SERIAL PRIMARY KEY, device_id INT REFERENCES(another_table), uptime TIMESTAMP Every device is pinged every N minutes. The application logic and ...
0
votes
1answer
1k views

Backing entire Postgres database server up with pg_basebackup

I back Postgres DB 9.1 up using following command: pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P In postgresql.conf I have this set: wal_level = hot_standby ...
0
votes
1answer
345 views

Postgresql multi-column GIN index [duplicate]

Suppose a postgresql table items like this. (keywords column is of type text[]) name account_id keywords ------------------------------ foo1 1 ['k1', 'k2'] foo2 1 ...