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)

4
votes
1answer
109 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 ...
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 ...
6
votes
2answers
641 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
0
votes
2answers
42 views

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

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
37 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 ...
0
votes
2answers
255 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
2
votes
1answer
135 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
22
votes
3answers
38k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
1
vote
0answers
16 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
2answers
40 views

PostgreSQL: result of range difference would not be contiguous

How to substract a smaller subrange from a bigger range? 123456789 567 Result: 1234 89 SQL: select '[1,9]'::int4range - '[5,7]'::int4range; ERROR: result of range difference would not be ...
1
vote
2answers
36 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
35 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 ...
2
votes
2answers
461 views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
1
vote
1answer
39 views

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

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 ...
3
votes
1answer
57 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 ...
0
votes
1answer
77 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 ...
0
votes
1answer
106 views

mongodb on-premise: will it work?

We are planning to package MongoDB-PostgreSQL hybrid solution in our application that will be deployed on customer site on-premise. It will be packaged as a software virtual appliance. Our reason for ...
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 ...
2
votes
1answer
476 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
1
vote
1answer
44 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", ...
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: ...
2
votes
1answer
150 views

Postgres query plans and tuning parameters

I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
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 | ...
0
votes
1answer
57 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
24 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 ...
1
vote
1answer
21 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
27 views

Migrate MySQL 3.23 Data

I have .FRM, .MYD and .MYI files from a MySQL (version 3.23 I think) database from back in the early 2000's. I managed to get the files into a Windows installation of 3.23 and then dump it, however I ...
1
vote
1answer
32 views

Call function where argument is a (sub)select statement

My function takes an int4 as argument and returns a table: SELECT * FROM test_function(545421); -- works fine SELECT * FROM test_function(SELECT customerid FROM tableX ...
1
vote
1answer
91 views

postgresql 9.4 + BDR Fails

I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start Actually, my sysadmin provided me with a package to test with. He's taken ...
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 ...
3
votes
1answer
159 views

Enforcing constraints “two tables away”

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short ...
2
votes
2answers
24 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 ...
1
vote
2answers
129 views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data ...
3
votes
1answer
750 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
2answers
44 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 ...
9
votes
1answer
8k views

Superuser is not permitted to login

I created a superuser portal with the following command create role portal with superuser password 'portal' When I tried to login into postgres with portal user I am getting the following error in ...
0
votes
1answer
127 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
0
votes
1answer
30 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 ...
0
votes
1answer
27 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 ...
1
vote
1answer
21 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 = ...
2
votes
2answers
123 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
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 ...
3
votes
1answer
138 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
0
votes
1answer
375 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
0
votes
1answer
61 views

How to remove duplicates and keep the one with the most additional information

I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses: Column | Type | Modifiers ...
0
votes
0answers
18 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 ...
0
votes
1answer
42 views

Installing PostgreSQL and PostGis on Mac OS X

I downloaded and installed PostgreSQL and PostGis from http://www.kyngchaos.com/software/postgres, I work on OS X 10.10. When I go into the terminal now and type psql it says -bash: psql: command ...