Tagged Questions
1
vote
2answers
80 views
PostgreSQL: return a select statement in a procedure
Is there any equivalent from MSSQL to PostgreSQL to return a select statement (which result could be from various tables)?
In MSSQL it would be like this:
CREATE PROCEDURE MyProc
AS
BEGIN
SELECT ...
0
votes
2answers
33 views
How can I find out if the values in an array exist in a Postgres table
I am trying to determine if values in a postgres array field correspond to values in another table.
I have a table: cars
id | name | contents
1 | Ford | {1, 3, 5}
and table: contents
id | name | ...
0
votes
2answers
125 views
postgresql trigger (strategy) to update table based on entries of another table
I am fairly new to PostgreSQL (spoilt by django ORM!), and I would like to create a trigger which updates a table based on entries of another table.
So, I have the following table on my schema:
...
2
votes
1answer
1k views
Querying on Json data type in postgres Rails-4
I am using Rails-4, have a Product model and stored specifications as JSon type
In Migration file, add
add_column :products, :specifications, :json
sample product record is look like
#<Product ...
2
votes
1answer
116 views
PostgreSQL deadlock between select for update even ordering
I have two query that deadlock together
PERFORM id
FROM stack
WHERE id IN (SELECT tmp.stkid FROM tmp_push_bulk tmp WHERE tmp.stkid > 0)
ORDER BY id
FOR UPDATE OF stack
And
PERFORM stk.id
FROM ...
1
vote
1answer
48 views
Pushing data from Postgres 9.2 to a remote database
I've used dblink several times in the past to pull information from a remote database into a local one. However, I find myself wanting to do the opposite. I want to PUSH from local to a remote. Per ...
1
vote
1answer
63 views
Constant column in child partition tables
We are using inheritance based partitioning in our application. The partitioning is on a column such that each partition has a different value for this column. Something like this:
CREATE TABLE base(
...
1
vote
1answer
76 views
Unable to infer SQL type for my user-defined function which return BigInt
My code:
def function1(userId: Long) = DB withConnection {
implicit con =>
val res = SQL(
"""
SELECT my_custom_function({userId}, {param2}) as "my_title"
"""
...
1
vote
1answer
90 views
postgres sql selecting by weekday including empty records in rails
I have a query which is 90% there, I'm querying data on whats going on for each day of the week which works fine for data that is there but I'd also like data for those that aren't (I want the query ...
1
vote
1answer
49 views
PostgreSQL : SSL with TCP connections
I am trying to configure SSL communication for PostgreSQL.
I have a self-signed cerificate and I am following these instructions as specified in URL ...
1
vote
1answer
114 views
Postgresql createdb command hanging when running from groovy script
I've tried several different ways to get this to run, all unsuccessfully!
Currently, I have a groovy script that tries to execute the Postgresql (9.2) 'createdb' command like this:
def ...
0
votes
1answer
11 views
generate json with column value as json dict key
I'm using postgres 9.2, and I'm trying to construct a query that generates json in a particular format. I've gotten pretty close with a simple json_agg expression, but now I'm stuck moving forwards.
...
0
votes
1answer
17 views
Can't disable order joins in Postgresql
Postgresql provides a parameter join_collapse_limit = 1 to disable order of joins. But when I set the parameter and reset the server, the query plan is not changed and there still is order join ...
0
votes
1answer
39 views
Why doesn't my Postgres SQL Query use an index
I have a table (sales_points) with ~23M rows. It has a b-tree index on (store_id, book_id). I would expect the following query to use that index, but EXPLAIN indicates that it is doing a sequential ...
0
votes
1answer
30 views
Can't use pgRouting functions in PostgreSQL 9.2
I have 3 pgRouting sql files,
routing_core
routing_core_wrappers
routing_topology
And I run the code in the first one in PostgreSQL 9.2, I got this error,
PS. although there are already ...
0
votes
1answer
29 views
Select same values from different schema dynamically based on a common table
I have a database consisting of mutiple schemas with same structure. This enables to segregate data based on enterprise. So there could be mutiple schemas like db_client1, db_client2 etc. and common ...
0
votes
1answer
16 views
Correct way in Postgresql 9.2 to call a function in a query
I noticed something strange today my log files. I see a "NOTICE" getting logged over and over again. And I think I've figured out the cause but don't under stand it or the fix.
The query is:
...
0
votes
1answer
244 views
Postgres slow query (slow index scan)
I have a table with 3 million rows and 1.3GB in size. Running Postgres 9.3 on my laptop with 4GB RAM.
explain analyze
select act_owner_id from cnt_contacts where act_owner_id = 2
I have btree key ...
0
votes
1answer
20 views
How to make query in postgresql which will find all records which are started more than 6 hours?
How to make query in postgresql which will find all records which are started more than 6 hours ago?
In my table I have a column which stores utc time of last time used in milliseconds (long ...
0
votes
1answer
47 views
closest values for each row
I have a table of prices of products, each price has a category. for each row representing a product I need to know the minimum range of prices in which the product price is contained, by product ...
0
votes
1answer
31 views
Replacing trigger procedure without deleting triggers
I have trigger procedure:
CREATE OR REPLACE FUNCTION my_func() RETURNS trigger AS
$BODY$
// Do something :-)
RAISE NOTICE 'Done';
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
It is used for ...
0
votes
1answer
91 views
Insert data in multiple tables at a time with repeated values
I have to insert data into first and second table directly. But the third table which I received data as array and inserted into 3rd table as same.
In my 3rd table values will be repeated. Ex:
...
0
votes
1answer
41 views
Determining free space after vacuum
With PostgreSQL 9.2, is there a way to determine the amount of space considered available within the database? More specifically, the approximate amount of space that can be used before the disk ...
0
votes
1answer
66 views
Data Recovery: PostgreSQL showing base volume under postgres pg_default tablespace, but does not recognize separate databases
I had an instance of Postgres (v 9.2), running locally on Windows 7. I have yet to isolate the cause, but PG became corrupted in such a way that the server abruptly stopped, and the service would ...
0
votes
1answer
539 views
PostgreSQL 9.2 archiving last replayed WAL after recovery
Following the PostgreSQL 9.2 official documentation, I have set up continuous archiving on my postgres.conf file:
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /.../archive/%f ...
-1
votes
1answer
26 views
What is difference between postgres users?
I'm new to postgres and have some questions.
I have installed postgresql on my linux system, and it created a user postgres on the system, also i have postgres user in pgadmin, are they the same user ...
1
vote
0answers
24 views
Updating a table depending on some conditions
There is table called payment_history.
payment_history
id
status_name
added_at
user_id
There are 3 statuses: paid, pre_active and active. There can be only one item with the status "active" ...
1
vote
0answers
48 views
How a simple and harmless left join can make a query so slow?
Why a simple and "harmless" LEFT JOIN on a specific table can make so much difference in the performance result? I have a primary table "mposicao" in the query that relate itself on two more tables on ...
1
vote
0answers
58 views
Dynamic calculation in postgres
I am trying to do a dynamic calculation in postgres
as following
drop table if exists calculation_t;
create table calculation_t(
Id serial,
calculation_logic varchar(50))
Inserted a calculation ...
1
vote
0answers
127 views
How to get value of a variable in plv8 without using plv8.elog()?
I am having an issue with getting correct output from plv8.elog(). Just to start, I'm using PostgreSQL 9.2, plv8 1.4.1 as installed by pgxn, on Ubuntu 12.10. I have a function I am building that ...
0
votes
0answers
24 views
“FATAL: the database system is shutting down” while creating connection to PostgreSQL
I am getting FATAL: the database system is shutting down errors while creating PostgreSQL JDBC connections to a PostgreSQL 9.2 server. The specific exception path I'm getting from JDBC is here:
...
0
votes
0answers
17 views
Debugging in postgresql
I want to be able to debug the functions and the sql quires in PdAdmin 1.18. I found almost nothing but this article about debugging postgresql Debug PostgreSQL function using pgAdmin which seems to ...
0
votes
0answers
34 views
inserting json key/values into postgres json field store
Is there a way to insert a key somewhere in JSON store in postgres ?
Say I have this data:
{
"CUSTA": {
"name": "Customer A",
},
"CUSTB": {
"name": "Customer B",
}
}
...
0
votes
0answers
14 views
Sendquery in postgresql extension
I write a serial of SQL functions as an extension, in one function I want to execute a query like "CREATE TABLE", may I ask using what function I can execute a query in Postgresql extension? thanks.
0
votes
0answers
48 views
Is it possible to insert 0 for boolean false and 1 for boolean true without typecasting in Postgresql 9.2
I am using PostgreSQL 9.2 and I want to insert 0 for Boolean false and 1 for Boolean true without using any typecasting, which is not accepting by PostgreSQL database.
Normally PostgreSQL allow ...
0
votes
0answers
36 views
Is it possible to the read data from bytea type field using getBlob() method in postgresql9.2
I am using postgresql 9.2 and ,when I read the data from Bytea(used to store binary large object data in postgresql) field using getBlob() method but I'm getting the same error
Error: type bad value ...
0
votes
0answers
33 views
How to change postgresql to store table names in uppercase without changing the way of accessing it
i am working on postgresql 9.2 and i am getting more difficulties with storing the table name in postgres in lower case. Is there any way so that we can store tables in upper case and retrieve using ...
0
votes
0answers
54 views
Is it possible to create “Insensitive Table and Column name” in PostgreSQL
I am working on PostgreSQL 9.2 and i want to create a table with capital letter without using double quotes.Is it possible by any how? means
CREATE TABLE STUDENT(NAME VARCHAR(50));
i want the ...
0
votes
0answers
21 views
Using Coalesce to Update Non Null Columns
I have a table which needs to be updated under different scenarios.
Column which needs to be updated are not constant and known only at run time.
I am writing a generic query using COALESCE, only ...
0
votes
0answers
42 views
PostgreSQL 9.2.4 - Desire to Change the Localhost Server Password in Mavericks & Databases
Currently PostgreSQL 9.2.4 is provided in OSX Mavericks. I have used the provided PostgreSQL since running Lion Server. When I started using it I was using a password that I now realize is not very ...
0
votes
0answers
25 views
Retrieve Data from pgagent of Postgresql
I've installed pgagent and I think that the schedule job is running well. But the problem is when I try to call the table using the command as follow
select* from pgagent.pga_job
What I got is just ...
0
votes
0answers
48 views
Indexing for efficient querying and pagination of financial data in PostgreSQL
I'm working in an API that needs to return a list of financial transactions. These records are held in 6 different tables, but all have 3 common fields:
transaction_id int NOT NULL,
account_id ...
0
votes
0answers
29 views
potgres query for the oracle query
what will be the postgres query for the following oracle query.
select CID,
max(cus.customer_id) keep (dense_rank first order by decode(acct.CUS_Type,'X',4,'M',5,'F',6,'G',7,'I',5,6), start_date) ...
0
votes
0answers
117 views
pg_basebackup PostgreSQL statement fails
I am trying to execute pg_basebackup command to take base backup but I do not see a success statement when I execute the command. Is there anything else where I went wrong?
pg_basebackup -h127.0.0.1 ...
0
votes
0answers
1k views
createdb: could not connect to database template1: FATAL: password authentication failed for user
Installed Postgresql via Homebrew. I wanted to create a new database after unzipping a ".tar". But I have this problem.
Michael-Adelekes-MacBook:wikiful madeleke13$ sudo su postgres -c
...
-1
votes
0answers
14 views
how to get a notification in PostgreSQL to check whether the database is running or not using shell script?
I want to write a shell script to check whether the database is running or not in PostgreSQL and If dead, then raise a notification to users along with some log information.
IST LOG: received smart ...
