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
12 views

Combine multiple query into 1 query

I am using postgresql. I have a message table. messages table contains words field, array type, it contains random words of a message Now I have an array of words [A, B, C] I need to find the number ...
-1
votes
0answers
26 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 ...
2
votes
0answers
29 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
16 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
17 views

postmaster dead but pid file exists

i am using postgres 9.2 on redhat 6 i'm a little confused as to why the following is happening with postgres 1.when postgres is running, performing "service postgresql92-postgresql status" as root ...
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
1answer
23 views

Waiting on ExclusiveLock on extension

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
14 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
0answers
13 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
26 views

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

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
15 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
19 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
23 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
14 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
21 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
26 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
16 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
48 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
11 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
15 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
10 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
30 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
0answers
41 views

The cost of left out join versus a select

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
29 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
21 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
14 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
44 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 ...
-1
votes
1answer
48 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
28 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
17 views

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

Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?
0
votes
0answers
31 views

where can i learn nosql for postgres [on hold]

Postgres has recently made major improvements on its handing of noSql. I would like to learn more about using nosql especially its document or blob store formats to create data for a chatbot I am ...
0
votes
0answers
26 views

What's the difference between a using partitioned table and using partial indexes based on the partition key? [on hold]

I have a users table that is almost always queried by the customer column. I'm looking into making those simple select queries more efficient. I came up with two ways to do it: Partition the table ...
0
votes
1answer
26 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
0answers
34 views

Building a database in Postgres in real life - psql cli, .sql script files, gui? [on hold]

I have been learning Postgres by reading tutorials, which assume you're typing commands one-by-one into the psql command line. That's fine in tutorial world. But in real life development, when ...
0
votes
1answer
25 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
17 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
40 views

Query across schemas in PostgreSQL [on hold]

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 ...
0
votes
1answer
31 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 ...
0
votes
1answer
31 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
34 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
33 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
26 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
22 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 ...
1
vote
0answers
33 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
30 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
23 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
1answer
19 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 ...