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)

0
votes
0answers
3 views

Log large queries with PGbouncer

Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
0
votes
2answers
24 views

How to use aggregate function and group by with inner join? (Postgres 9.3)

I would like to select "extra" payments made in association with normal appointments for a one week period and SUM them up by contractor when calculating payroll. Here's what I attempted: SELECT ...
-1
votes
0answers
55 views

Should I use array in column or not? [on hold]

I have the following table: CREATE TABLE profits ( id serial NOT NULL, matriz name, profit2013 double precision[], profit2014 double precision[], profit2015 double precision[], CONSTRAINT ...
0
votes
0answers
19 views

How to catch code of error which occured inside plv8 function?

In plv8 function I am executing (plv8.execute(...)) INSERT INTO ... query which violates foreign key constraint. However pg client receives following (internal) error instead: { [error: Error: ...
3
votes
1answer
70 views

PostgreSQL function definition “syntax error at or near +” [on hold]

Suppose a simple query: (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id) + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id) - (SELECT MAX(timestamp) FROM events e ...
0
votes
0answers
14 views

How to move a specific Postgres database to different harddrive?

I have my postgres db running on harddrive d:\, and also all the tables are placed there by default. Now I want a specific table to be save to a different drive (say f:\). The table is not yet ...
1
vote
1answer
13 views

missing WAL file on standby server following a backup

I seem to have an intermittent problem with my postgres backup/restore to standby Postgres, is version 9.2 running on red hat 6 enterprise i have a production server (PROD1) and a hot standby ...
0
votes
0answers
36 views

PostgreSQL: Best way to INSERT for vertical search engine [on hold]

I'm trying to develop vertical search engine running on PostgreSQL & Go in Linux server. Data harvesting using Linux command line. For the INSERT query: The "best" I means is safe & high ...
1
vote
1answer
26 views

Is it possible for a client to kill a long-running Postgres query?

A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on ...
1
vote
2answers
23 views

What to do with WAL files for Postgres Slave reset

So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error: FATAL: could not receive data from WAL stream: FATAL: ...
0
votes
1answer
10 views

Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria?

I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment ...
1
vote
2answers
23 views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
1
vote
2answers
46 views

Anything like a hash for Postgresql databases?

I have an export process I want to check. I want to verify that source and destination databases are equal. There are about 25000 rows, and schema is the same. Is there a way to somehow "hash" the ...
0
votes
1answer
23 views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
1
vote
0answers
23 views

PostgreSQL DB freezes with large inserts

I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
0
votes
0answers
17 views

What is the best way to analyze free space in a postgres database and the grow of usage?

In Oracle you can in datadictinary analyze free space by using SQL like: select b.tablespace_name, b.file_id, b.file_name, b.bytes/1024 total_KBytes, sum(a.bytes)/(1024) sum_free_Kbytes, ...
1
vote
1answer
21 views

Does index occupy disk memory for rows for which index expression is NULL?

Let's assume that I have following pg_trgm index on mydata JSONB column: CREATE TABLE mytable (mydata JSONB); CREATE INDEX trgm_index_1 ON mytable USING gin ((mydata#>>'{myfield}') ...
1
vote
0answers
12 views

PostgreSQL security threats in external modules or PL languages

In reading the answer to question I came across a paragraph that indicated that some PostgreSQL PL languages and/or contrib modules can be used to compromise the security of the system. Is this only ...
1
vote
1answer
32 views

pg_ctl status responds with “available” during failed startup

I am using pg_ctl -D $PGDATA status to monitor whether my postgres standby database is up and running. Sometimes the database fails to start due to a missing WAL file, but its status is reported as ...
0
votes
2answers
42 views

How to use except but excluding a column from the except clause comparison -psql? [on hold]

I want to compare two sets of columns but exclude one column from the comparison as it will never be in the second table and I need it on my result. So, explaining: columns for both tables: ID ...
1
vote
1answer
30 views

Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

How come that statement don't copy constraints if I explicitly ask for it? CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ...
1
vote
0answers
14 views

Optimizing a geo search in Postgres

I'm searching for UK postcodes within a radius of a given postcode. The query that I'm using looks like this: SELECT *, earth_distance(ll_to_earth( {$lat}, {$lon} ), ll_to_earth(lat, lng)) as ...
1
vote
1answer
32 views

Can't connect to EC2 Postgresql instance unless I change the default port 5432 [on hold]

I just created a brand new EC2 instance with a default security group allowing All Traffic from Anywhere. My listen_address is set to * I can SSH to it but I can't connect to the pgsql instance ...
2
votes
1answer
52 views

Can this UPDATE be faster with indexes?

update line_items set product_id = dups.latest_product_id from line_items as li join products p on li.product_id = p.id join vendors v on p.vendor_id = v.id join vendorgroups vg on ...
0
votes
1answer
41 views

Limit the number of votes

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes. I can think of two ...
1
vote
0answers
21 views

debugging PostgreSQL serialization failures

I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format: ...
1
vote
1answer
41 views

PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

Could I create a VIEW from the JSON data type but have it as a relational structure? Example: SELECT * FROM json_test; Results [ { "name": "Roy", "Country": "USA", ...
4
votes
1answer
100 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
0
votes
1answer
52 views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
3
votes
1answer
50 views

Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform)

I have PostgreSQL 9.4 installed on a dual-boot machine (both on Win 7 and Ubuntu 14.10). The two systems share a drive (NTFS-formated) which includes a PSQL database directory which was created under ...
1
vote
2answers
33 views

postgreSQL hot_standby pg_dump on slave

I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump. I switched to postgres from MySQL. With MySQL ...
1
vote
1answer
28 views

RHEL 6: postmaster dead but pid file exists

I am using PostgreSQL 9.2 on RedHat 6 I'm a little confused as to why the following is happening: When postgres is running, performing "service postgresql92-postgresql status" as root results in ...
0
votes
0answers
15 views

Nicely managed lookup tables [migrated]

We have a people table, each person has a gender defined by a gender_id to a genders table, | people | |-----------| | id | | name | | gender_id | | genders | |---------| | id | ...
3
votes
2answers
53 views

Performance hickups after Postgres 9.4 upgrade

After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into ...
0
votes
0answers
16 views

How to display additional column in select with true/false value [SOLVED]

Solved. Look in comment. I am using PostgreSQL 9.1. I have 3 tables: simcards: id serial NOT NULL, iccid character varying(30) NOT NULL, customer_id integer customer: id serial NOT NULL, name ...
0
votes
1answer
34 views

Is anyone else experiencing consistency issues with postgres-xl?

Just like the title says. I have been playing with postgres-xl for over a month and have been seeing strange behaviors. I am using the docker container referenced from the postgres-xl.org site and ...
0
votes
1answer
33 views

What is the recommended source control to use with PostgreSQL and SQLLite? [closed]

So I'm in varsity and we have been tasked with building a mobile app and a website that are linked. We are a team of 6 and our app and website needs a database. We have decided to go with ...
0
votes
0answers
16 views

rt_raster_to_gdal: Could not load the output GDAL driver

I have already set the environment in windows SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; Even I tried by adding following in environment path- POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL ...
2
votes
2answers
23 views

Aggregate all joined rows with where clause

Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects. Each project has an ordinal assigned, 1-5 (step_number). I would ...
0
votes
2answers
42 views

Automatically set value on child rows when deleting parent row?

Description We have a table and data below : Version: PostgreSQL 9.3 Table name: tree_data (id int, code text, name text, parent_id int) Primary key: id Foreign key: parent_id (refer to id) and ...
1
vote
1answer
19 views

Unable to create read only user— postgresql 9.4

I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema. I have tried many different ways to get this but using REVOKE ALL on schema public to user1; GRANT ...
0
votes
1answer
25 views

Insert sum of the row data of each record into separate column in postgreSQL?

I have a table id name class social science math Total 2 Max Three 85 56 85 3 Arnold Three 55 40 75 4 Krish Four 60 50 70 ...
0
votes
0answers
19 views

Postgresql Query equivalent to VLookUp of Excel [migrated]

I have two tables such as Table1 and Table2. Table1 contains three columns called name, age and marks where marks column is empty and I need to populate from Table2. Table2 contains two column called ...
0
votes
0answers
28 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
0
votes
1answer
21 views

Error in PostgreSQL/PostGIS Access to offline bands disabled

I am using postgis functions i.e. ST_Clip, ST_PixelHeight. But I am getting this error - ERROR: rt_raster_load_offline_data: Access to offline bands disabled Actually, I have loaded my raster ...
1
vote
1answer
19 views

How to cancel query as non-superuser?

I'm a non-superuser on a PostgreSQL 9.1 database, and I've just created a bad trigger function that won't stop. I can see the process, e.g.: thedb=> SELECT * FROM pg_stat_activity WHERE procpid = ...
0
votes
1answer
56 views

Inaccurate count when using multiple count functions in PostgreSQL

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.890 I'm relatively new to SQL and am learning a lot of about writing queries as in ...
0
votes
0answers
14 views

Multilingual Article/ Category database design

I wasn't getting any answers over at Programmers and was advised to go over here instead... I wanted to make a design for a multilingual database and after having drawn it up have arrived at 5 ...
1
vote
1answer
17 views

Insertion Rule on a view

I m using postgres 9.3 and i m trying to insert data to the view objectifsBoutique and and PostgreSQL returns following error(« objectifsboutique » relation is not found). I have to mention that the ...
0
votes
0answers
16 views

installing pgagent on centos

I have installed pgagent on centos. Now I am trying to connect it to postgres database. I ran: /usr/bin/pgagent_94 -l 2 host = 127.0.0.1 dbname = postgres user = postgres port = 5432 password=mypass ...