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
2answers
48 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
0
votes
1answer
63 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. ...
0
votes
0answers
43 views

How to check/know the highest run queries

I would like to gather statistics on the highest run queries in my database. For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
-2
votes
1answer
53 views

Permission Denied on a database created by the same user

I run this command: psql --username=openerp --dbname=sf_template_20150608111121 --no-password \ --command="update pg_database set datallowconn = false \ where datname = ...
0
votes
1answer
25 views

Postgres DB user permission

I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in PostGres. When I select ...
1
vote
1answer
22 views

Using the value of a JSON object that is stored in a PostgreSQL JSON array

I have a table that looks like this (simplified): Jobs ------------------------------- ID | Details ------------------------------- 1 | { 'tasks': [] } 2 | { 'tasks': [{'id': '1', 'due_on': ...
0
votes
1answer
38 views

the cache hit rate using pg_statio_user_tables

I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure ...
2
votes
3answers
101 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
1
vote
0answers
30 views

BDR Replication issue

I am trying to setup 2-node BDR replication and i am getting the following error while running bdr_init_copy. /usr/pgsql-9.4/bin/bdr_init_copy -d "host=10.10.145.111 dbname=apimgtdb port=5432" ...
0
votes
1answer
30 views

Cannot rename database (the user in --username is the owner)

I connect with psql and list my databases: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...
0
votes
1answer
23 views

Postgres - returning custom values ie. “true” rather than “t”

I am wondering if there is a way to show custom "values" in the output of a query purely for readability, similar to the AS statement for renaming column labels. Seems unlikely but would be useful ...
0
votes
0answers
28 views

Check if key exists in a JSON with PL/pgSQL? [migrated]

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL function. Here is the function. CREATE FUNCTION sp_update_user(user_info json) RETURNS json LANGUAGE plpgsql AS ...
1
vote
1answer
37 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
3
votes
3answers
144 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
1
vote
0answers
38 views

What is the best way to clear couple of tables each night at specific hour in Postgres SQL?

I need to clear couple of tables every night on my db so in the morning they will be empty and ready to use for the end users. I already wrote the query to truncate them but I'm not sure what is the ...
0
votes
0answers
27 views

Foreign table not using index

I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this ...
0
votes
1answer
25 views

Point PostgreSQL on Ubuntu to a new datadir

I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install. I've been trying to move the PG dbs from the old ...
0
votes
1answer
25 views

How can we configure the Streaming Replication(master-slave) on single SAN storage in PostgreSQL?

I want to configure the Streaming Replication in PostgreSQL.My data directories of Primary and Standby are in same SAN storage.Is it Possible to do replication.
0
votes
0answers
26 views

How can I set a value after Select query in PostgreSQL? [migrated]

I use simple select query: SELECT transactiontype FROM posfeed LIMIT 100; I result I have some rows with empty values. I need set some default value in SELECT query result for some filed. ...
1
vote
1answer
43 views

Algorithm used to perform offset/limit over a union all result set

I have two result sets: rs1: id name serial text ________________ 1 Nick .................... 1233112 Pete rs2: id name serial text ...
0
votes
1answer
17 views

postgresql : find privileges in meta informations

With PostgreSQL 9.4 on linux centos 6.6. I give a privilege to a user with something like : ALTER DEFAULT PRIVILEGES FOR ROLE DBNAME_adm IN SCHEMA DBNAME_adm GRANT SELECT,INSERT,UPDATE,DELETE ON ...
0
votes
1answer
34 views

Postgresql :i want tro truncate column

I have select * from generale where titolo ~* 'hp-ux' order by titolo; autori | titolo ...
1
vote
1answer
21 views

Query single column, all rows in Postgres 2D Array

Is there any way to fix the query below so I don't have to specify an arbitrary max row count? DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, ...
0
votes
0answers
32 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
1
vote
1answer
23 views

Postgresql ignoring a substring in the middle of a string

I may have phrased the title of this question badly. I have a documents table that has a filename, path, and file_content_type columns. I want to search against the filename column to determine if ...
0
votes
1answer
23 views

Possible to use a field within a query in PostgreSQL?

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this: SELECT * FROM events ...
0
votes
1answer
18 views

Select row if column data was not selected

How can I selected rows from a table when a column data is different. Table: id(pk), name, title, book_id, date I want to get all the rows where book_id is uniq. Data example: test1, test2, 1, ...
0
votes
1answer
44 views

What is the purpose of the keyword “||” in PostgreSQL? [closed]

I'm new to PostgreSQL and I'm trying to figure out what does the operator || mean? Google didn't help with that...
0
votes
1answer
23 views

Postgresql select columns in join query [closed]

say that i do inner join in query... the result will give columns from both table. Is there a way to specify that i want only columns from one of the tables without specifying all columns names ...
2
votes
1answer
42 views

is it possible to run postgres with no WAL files being produced?

I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. We have a end of day batch process which generates a large number of WAL files (and has ...
0
votes
0answers
17 views

Postgresql list all functions associated with table and column [duplicate]

I have a table named SW and in it a column named ID. I want to list all functions from all Schemas that use this table and this column. I've wrote the following query: SELECT n.nspname AS ...
0
votes
2answers
51 views

PostgreSQL: how do I list all triggers that use a specific field?

for example i have a student table with (studentid) i want to get a list of triggers that use this field... for example if in table X there is a trigger that uses studentid i want it to be shown in ...
0
votes
0answers
47 views

PostgreSQL -> MySQL Migration with MySQLWorkbench

I'm trying to migrate data from a PostgreSQL DB to a MySQL DB (it's for permissions in the VPC, so please nobody link me the 'choose-something-else'). I'm getting this error from MySQL Workbench when ...
1
vote
0answers
24 views

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' ...
3
votes
1answer
61 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
0
votes
0answers
10 views

Query table with the same name on different schemas in PostgreSQL [duplicate]

I want to query table with the same name (for instances "walls") in multiple schemas on a PostgreSQL database. Is it possible? I am searching for something like this: select * from *.walls; I need ...
0
votes
3answers
42 views

Boolean check on fields returning any of them except if all have same value

Assuming we have 4 boolean fields a,b,c,d How can we return any of the fields that are true, but not nothing when all 4 are true? where a=true or b=true or c=true or d=true will return a row where ...
0
votes
0answers
36 views

How to list all tables associated with specific foreign key?

I have this Postgresql query: select (select r.relname from pg_class r where r.oid = c.conrelid) as table, (select r.relkind from pg_class r where r.oid = c.conrelid) as type, (select ...
1
vote
0answers
32 views

postgresql Insert into table B based on foreign key in table A, if exists in A else

the scenario is as follows: I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier ...
0
votes
0answers
23 views

postgresql view all tables with specific FK

I have a table of suppliers with supplierID (PK) and supplierName in Schema X I want to find out all tables names in all Schemas where the supplierID is FK there. How can i write a query that does ...
0
votes
0answers
28 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
2
votes
0answers
50 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> ...
2
votes
1answer
57 views

PostgreSQL force upper case for all data

Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?
0
votes
0answers
17 views

postgresql how can i get list of trigger which are fired when function X is working?

my goal is to list all trigger names which are working when function X is working. i assumed it has something to do with pg_proc and pg_constraints but i couldn't figure it out.
0
votes
1answer
22 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...
3
votes
1answer
36 views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h ...
1
vote
1answer
30 views

Returning case-sensitive input of case-insensitive array match

Taking a JSON table with some simple data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}'); INSERT INTO jsonthings(d) ...
0
votes
1answer
36 views

Adding elements into PostgreSQL json array field

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION ...
0
votes
2answers
41 views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
1answer
24 views

Postgres one-liner import & export SQL without login & without asked for password

I'm looking for one-liner command for import & export SQL without login into postgres role or psql prompt. Below is what I got for import SQL, but it asking password every time: psql -h ...