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
0answers
24 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
50 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
115 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
155 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
75 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
43 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
67 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 ...
3
votes
2answers
91 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
18 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
70 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 ...
1
vote
0answers
37 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 But ...
2
votes
2answers
25 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
52 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
30 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
33 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
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
34 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
23 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
61 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
17 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
29 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 ...
1
vote
1answer
34 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 ...
0
votes
1answer
77 views

The cost of left out join versus a select [closed]

I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3 I turned on logging for the SQL queries to test some code behavior. So, assume 2 tables, OneToMany ...
1
vote
2answers
45 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 ...
0
votes
1answer
24 views

How does PostgreSQL decide which query to cancel during deadlock cleanup

I know that PostgreSQL will detect and clear deadlock once it happen by cancelling one of the queries. How does PostgreSQL decide which one to cancel? Is this deterministic or there is some randomness ...
0
votes
0answers
20 views

How can I use PostgreSQL's partial sum tree index (if it even exists)? Do any databases allow for use of such an index?

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ...
1
vote
1answer
52 views

What difference between these 2 SQL styles?

If I have a simple table named: conn_log in my Redshift/Postgresql In the performance of view, what difference between these two commands ? select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn ...
0
votes
1answer
68 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
34 views

For Postgres, how would I define a custom function to generate primary keys?

In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key. How would I do this in Postgres?
-3
votes
1answer
20 views

In Postgres, what is the relationship between a SEQUENCE and a SERIAL? [closed]

Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?
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 ...
0
votes
1answer
27 views

Are file locations stored in PostreSQL catalog?

I am trying to learn a bit of PostgreSQL, and I am currently trying to understand the catalog. I think that if it stores all the metadata of the DB, should it also store things like the file ...
0
votes
0answers
24 views

New Table vs Java Beans to allow user to dynamically change data representation?

I am building a database to store the assessment records of students, along with other relevant information and functionality like reporting on average scores etc. Currently my company uses Excel for ...
0
votes
1answer
42 views

Query across schemas in PostgreSQL [closed]

In PostgreSQL, I have a table which, due to sharding, exists across many schemas. In sub-optimal cases, I need to execute a query on all of these schemas (due to an exception which violates the ...
1
vote
1answer
51 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 ...
1
vote
1answer
44 views

Postgresql query history hacking

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious ...
0
votes
1answer
32 views

Simple join in postgresql [closed]

I have a simple join query below : select jobs.location_id from companies, jobs where jobs.company_id = companies.id and companies.name = 'name'; it doesn't seem to get the job location of ...
0
votes
3answers
39 views

Last occurance of time relative to now()

What's the most elegant way to ask Postgres for the last 4:05pm relative to now()? I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much ...
0
votes
0answers
35 views

Forcing FK constraint to validated in system catalog

I have a big table (~60gigs, 35mill+ rows) that I need to add a FK to. Adding a FK directly to the table causes a validation which runs for about 30secs that I want avoid as it's a 30sec access ...
0
votes
1answer
66 views

Postgres privileges: psql \dp - what is about the plus-sign

I used psql-command "\dp" to find out the given grants. Versions on test-server: PostgreSQL 9.1.15 on armv7l-unknown-linux-gnueabi, compiled by gcc (Debian 4.6.3-14) 4.6.3, 32-bit Out of the ...
0
votes
0answers
35 views

Postgresql Hot Standby - How to determine it's fully mirrored?

I have set up a hot standby of a postgresql server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have ...
0
votes
1answer
48 views

Is there a timeout option for remote access to PostgreSQL database?

I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a ...
1
vote
0answers
61 views

Postgres-XL does not insert rows

I have successfully installed and started postgres-xl on localhost(Ubuntu 14.04). (Two datanodes, 1 coord) The problem is: 0 rows after insert CREATE TABLE test1 ( id bigint NOT NULL, profile ...
0
votes
0answers
32 views

Postgresql match all in full text search

Is there anyway that we can specify the tsquery to match all ? I have tried SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery; but it doesn't seem to work. It returns ...
2
votes
1answer
31 views

Converting a GraphDB schema to a relational database schema

I currently am using a graph database and am considering moving off of it to a relational database like Postgresql. My concern is that I have lots of relationships between entities (since this is ...
0
votes
0answers
13 views

A PostgreSQL user with SUPERUSER and all permissions to particular database can't read a specific column [on hold]

I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPERUSER, using the user postgres. I still can't read a specific column (which is ...
0
votes
1answer
23 views

Behaviour of Bulk Insert in pgsql or any Database

I'm using PostgreSQL 9.2.4. I'm working with multiple tables with large number of data. I have two tables Table_One which has one of the field with varchar(250) and Table_Two with one of the field ...
6
votes
1answer
333 views

Why is a new user able to create tables in PostgreSQL?

I followed two tutorials to create a DB with: a fully privileged user link a read-only user link I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to ...
1
vote
0answers
38 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...