All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
1answer
32 views

PostgreSQL use NEW in query for INSTEAD OF trigger

I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario: CREATE TABLE Product ( product_id ...
1
vote
1answer
20 views

Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian

After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system. I want to have 9.4 serve my data and get rid of the outdated version. AFAICS, it is important to use a ...
-4
votes
0answers
38 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
3
votes
1answer
44 views

Slow query on primary server runs fast on hot standby

I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
3
votes
0answers
37 views

Postgres 9.1 query fast second time

Postgres version: PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Open psql and run query. Takes 80ms. Run same query again. Takes 11ms ...
0
votes
0answers
29 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
17 views

how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? [duplicate]

I follow this URL to make some SOP for my colleague. How to update the parent/child of all rows in the tree (ltree)? However, my colleague hope to use some simple statement about "UPDATE" for adding ...
1
vote
4answers
65 views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
0
votes
2answers
44 views

Slow UPDATE FROM Query in Large Table

I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight. My basic query is: UPDATE us_demand SET ... FROM ...
1
vote
0answers
9 views

Raspberry as data aggregator node, transfer to database if Raspberry is online

I have never used SQL and currently I do read and watch various turorials and the manual of PostgreSQL. I would appreciate if someone could give me a brief overview how an experienced user would set ...
0
votes
1answer
18 views

Convert timestamp type during INSERT

I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp ...
0
votes
0answers
10 views

Security of SQL Trigger Using dynamically stored table name

I'm working with PostgreSQL 9.0 and am learning how to use Triggers to enable some useful functionality. I have an insert trigger on a table that looks like this: feature_id | layer_name| latitude | ...
0
votes
0answers
13 views

How does postgresql handle tablespace locations in multiple clusters?

I have a server running a single postgresql cluster with a tablespace defined like: CREATE TABLESPACE secondary LOCATION '/data_secondary/main'; I have a second server where I have two postgresql ...
0
votes
3answers
42 views

What data type for a column which can have three states: yes, no, unknown

I have an application where the user can decide between: yes no unknown I started to use a boolean column which is nullable. But things get quite complicated since my framework (django) does not ...
7
votes
1answer
408 views

Delete from table rows where any of the column field is null

Is there a way to delete a row from a table where any of the column field is null without specifying explicitly which column is null? I am using postgreSQL. Here's my relation schema: Column |...
0
votes
1answer
36 views

Possible to use first CTE as filter for second CTE?

I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ...
0
votes
1answer
25 views

How can I DRY up a series of upserts of the same fields from multiple tables?

I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column. I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a ...
3
votes
1answer
119 views

How to get max value of a column from one table with its matching record in second table in PostgreSQL?

I have two tables as follows: create table two(grade_id int, edu varchar(20),sortby int); alter table two add constraint pk_one primary key(grade_id); create table one(id int, name varchar(20),...
3
votes
2answers
55 views

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.) Instead of having Boolean columns, I could have a single array ...
3
votes
2answers
34 views

How do I get postgres to log the stored procedures it is running?

I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log. I've got it logging all the ...
1
vote
1answer
19 views

Persisting Prepared Statements

I know that the documentation states: Prepared statements only last for the duration of the current database session. Is there any way way to either save a prepared statement create code which ...
0
votes
2answers
47 views

Add database-level collation to PostgreSQL

So I have added a en_US collation to my system: # locale -a C en_US.utf8 POSIX sk_SK.utf8 Based on Postgres' documentation I have added it to my Postgres instance: postgres=# select * from ...
1
vote
2answers
32 views

Postgresql Log message without its statement

If I want to write a message to the Postgres log I can do this: DO $$ BEGIN RAISE LOG 'Justin Is Awesome'; END $$; However, it logs the statement as well so I get 5 lines of output instead of one:...
0
votes
2answers
30 views

Are there any Database functions which allow access to other network services in PostgreSQL

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure: Database functions which allow access to ...
1
vote
1answer
23 views

How to insert a record returned by a function into a table

I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record. CREATE FUNCTION my_func( IN id bigint, OUT foo bigint, OUT bar double precision ...
0
votes
0answers
19 views

How to delete/disable Extended SQL functions or turn off extended stored procedures in postgres

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure how to implement: Extended SQL functions ...
1
vote
0answers
45 views

SQL Server: How to select a snapshot for a transaction? [closed]

PostgreSQL has a feature to select a snapshot for a transaction using: SET TRANSACTION SNAPSHOT snapshot_id Does SQL Server have a similar feature? Edit: Answer to: What is the use case? In an ...
0
votes
0answers
18 views

Postgres dump fails on higher version

I'm trying to replace the current production database with staging. The production database version is 9.3.9 and staging is 9.3.10. Importing the dump on my local machine (which has version 9.3.4) ...
0
votes
0answers
6 views

Postgresql: replica and HA

I've postgresql 9.4. I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation. But anyway, I can't understand in a simple way what's the right way to have a ...
0
votes
1answer
27 views

Have DB users share tables

I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying ...
0
votes
0answers
33 views

Postgres: Understanding Hash Left Join?

Using Postgres 9.4, I'm trying to a query by conditions ==> no hash left join. EXPLAIN ANALYSE SELECT CEAR.RESULT_ENUM_TYPE_ID, CEAR.CE_CE_COMMUNICATION_EVENT_ID, MC.CAMPAIGN_NAME, MC....
1
vote
1answer
32 views

Postgres - MultiXactId error

I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup: ERROR: MultiXactId 1092514672 has not been created yet -- ...
0
votes
1answer
46 views

How to update value in inserted row in PostgreSQL?

I have a table t1 create table t1 ( id int primary key not null, name varchar(64), str2 varchar(64) ); I want to get the row ID and update a field str2 in this row. I tried this: WITH ...
0
votes
2answers
27 views

Postgresql pg_hba.conf changes are ignored or not loaded

Using Postgresql 9.3 on Ubuntu 14.04.4 LTS I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely. ...
0
votes
0answers
24 views

Can I write a constraint that requires two rows joined via a junction table to both have the same foreign key in Postgres? [duplicate]

I have a database of data collection instruments (e.g. questionnaire, test, etc.) based on the DDI standard. I am allowing categories to be reused within an instrument for multiple code lists, but ...
1
vote
1answer
22 views

Postgres permission for EXPLAIN

I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?
0
votes
1answer
24 views

Disable trigger effects in PostgreSQL

I have a function where there is: alter table a disable trigger trig1; some code alter table a enable trigger trig1; It basicaly disables the trigger for the purpose of specific operations. My ...
1
vote
0answers
25 views

Postgresql/AWS RDS Problem with high write IOPS

I am currently hosting a ~10GB database in amazon RDS running postgresql. I have 100GB of space allocated using General Purpose SSDs which means that I get 300IOPS to be split between read and writes. ...
2
votes
2answers
71 views

Fastest query for selecting arrays that contain duplicates

I'm using Postgres 9.5 and I have a column, named phones, of type text[]. I need to find all rows where this column contains duplicates. I've found this extremely useful set of functions https://...
0
votes
1answer
9 views

postgres index for “?|” JSONB operator

I have a jsonb row with objects/dictionairies : {"a":1, "b":2, "c",3} I want to query rows that contain a set of keys, ex: SELECT * from my_table v WHERE v.my_jsonb_column::jsonb ?| array['b','a'] ...
1
vote
1answer
24 views

is shared storage safe in Postgres if all servers are read-only?

Description of shared storage in Postgres: https://wiki.postgresql.org/wiki/Shared_Storage Related question: http://serverfault.com/a/620315/137464 Both of these make it clear that it's not safe to ...
1
vote
1answer
63 views

How do I insert a record and put the ID into an existing record in another table?

I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy. Simplistic example: create table aaa (id serial primary key, foo int);...
2
votes
1answer
136 views

How to constraint a foreign key to a subset

I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job. People ...
0
votes
1answer
28 views

Use system timestamp comparison in SELECT query

I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
1
vote
1answer
30 views

Database design. And get last modified time with each column in PostgreSQL

My database/table each endpoint/scope have multiple table, e.g user: user, user_information, user_role ... I was wondering should I separate table like below, or not? This is my first time to ...
1
vote
2answers
47 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
0
votes
0answers
17 views

Backup and Restore Hive Metastore in Postgres

How to backup and restore hive metatstore in embeded postgresql for CDH5.7.0 cluster? If the table is drop, then how to restore the metadata for that particular table in embeded postgresql.
0
votes
1answer
24 views

how to check list of users present in all postgres database

I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like, List of roles ...
0
votes
1answer
26 views

PostgreSQL - cannot create RULE from stored procedure

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
-1
votes
0answers
17 views

PostgreSQL Table Function: return all table fields

I have already created a view which has my derived columns with appropriate aliases. I would like to create a table function which uses this view and returns all the columns from that view. I have ...