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)

0
votes
1answer
33 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 ...
2
votes
1answer
52 views

Combine a generated series with each row from a select

The below select is returning an array of all the projects owned by all my users. WITH user_projects AS (SELECT u.id as user_id ,COALESCE(array_agg(DISTINCT pa.project_id), '{0}'::integer[]) as ...
0
votes
1answer
18 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) ...
0
votes
0answers
12 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 ...
2
votes
3answers
56 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 ...
0
votes
0answers
23 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
39 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. ...
1
vote
2answers
18 views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...
2
votes
1answer
42 views

How can I identify queries that are producing cache misses in Postgres?

I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory. The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%. I am trying ...
2
votes
1answer
52 views

PostgreSQL 9.3: Index on to_char()

How can I index a to_char() of a column? I have tried: adam_db=> CREATE INDEX updates_hourly_idx ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00')); But got the error: ...
0
votes
1answer
35 views

Storing not very volatile information in database with cache or mongodb

For a new project I'm working on right now I was doubting on which database/storage system to use for specific information which does not change that often. We have for instance some ...
0
votes
0answers
20 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 ...
1
vote
0answers
24 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
40 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 ...
0
votes
1answer
24 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
23 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. ...
2
votes
1answer
39 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 ...
0
votes
2answers
25 views

no pg_hba.conf entry for host “10.1.1.91”, user “user” database “testing”, SSL off

I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've ...
1
vote
0answers
50 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 | ...
0
votes
2answers
46 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
2answers
240 views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then on the application some times, we only ...
2
votes
1answer
56 views

postgresql 9.3: two instances on same port and two different ip adresses don't work

i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same ...
2
votes
1answer
33 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 ...
1
vote
0answers
24 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 ...
0
votes
1answer
20 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
24 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 ...
2
votes
2answers
72 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
1answer
32 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 ...
1
vote
1answer
54 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
56 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 ...
0
votes
1answer
55 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
30 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 ...
2
votes
1answer
25 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
38 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 ...
0
votes
0answers
16 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 ...
1
vote
1answer
45 views

Writing a view that caches to another table

I have a table market_trades with columns timestamp, market_id, and amount: api_production=# \d market_trades; Table "public.market_trades" Column | ...
0
votes
1answer
32 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
33 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
41 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 ...
0
votes
1answer
70 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
0answers
34 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, ...
0
votes
1answer
9 views

Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)

When I execute: CREATE VIEW foreign_keys AS SELECT tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM ...
0
votes
2answers
39 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
1answer
15 views

Does PostgreSQL `CLUSTER` command also cluster extended storage?

I'm trying to improve the locality of my data by using the CLUSTER command to periodically cluster records that share the same user_id. My most important data is in extended storage. Will extended ...
1
vote
1answer
43 views

PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?

I have run out of space, because I am new to Postgres and I have just not been monitoring it properly. Now I cannot even vacuum the tables: ERROR: could not extend file "base/20851/11787": No space ...
0
votes
4answers
88 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
64 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
1answer
16 views

Is there a benefit to using FOR SHARE in this PostgreSql simple select?

In the following PgSql stored function: CREATE OR REPLACE FUNCTION get_offer_from_id( offer_id bigint) RETURNS json AS $$ DECLARE res "entity_data_result"; r "get_offer_data"; ...
3
votes
2answers
47 views

idiom to treat empty result set as zero

This is obviously a SSCCE. I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple: CREATE ...