Tagged Questions
0
votes
1answer
17 views
Need cleaner update method in PostgreSQL 9.1.3
Using PostgreSQL 9.1.3 I have a points table like so (What's the right way to show tables here??)
| Column | Type | Table Modifiers | Storage
...
0
votes
1answer
19 views
Dynamic FROM clause in Postgres
Using PostgreSQL 9.1.13 I've written the followed query to calculate some data:
WITH windowed AS (
SELECT a.person_id, a.category_id,
CAST(dense_rank() OVER w AS float) / COUNT(*) OVER ...
0
votes
0answers
23 views
Converting two column of data to proper tabular format
I'm having the data in format given below.
Col1 | Col2
_______________
item1 | orange
item2 | apple
item1 | guava
item2 | noodles
...
I want data to be in below format.
item1 | item2
...
1
vote
0answers
47 views
Postgresql Crosstab with Array row_name
I have the following SQL statement. The inner query ('SELECT ARRAY...ORDER BY 1,2') works correctly and gives the correct totals for each row_name. When I run the crosstab, the result is incorrect. ...
2
votes
3answers
90 views
Changing ORDER BY from id to another indexed column (with low LIMIT) has a huge cost
I have a query on a 500 000 row table.
Basically
WHERE s3_.id = 287
ORDER BY m0_.id DESC
LIMIT 25
=> Query runtime = 20ms
WHERE s3_.id = 287
ORDER BY m0_.created_at DESC
LIMIT 25
=> Query ...
3
votes
2answers
38 views
Crosstab splitting results due to presence of unrelated field
I'm using postgres 9.1 with tablefunc:crosstab
I have a table with the following structure:
CREATE TABLE marketdata.instrument_data
(
dt date NOT NULL,
instrument text NOT NULL,
field text NOT ...
0
votes
1answer
25 views
PostgreSQL Foreign Key Constraint cannot Find Existing Key
I have this table:
CREATE TABLE operation_history
(
id bigserial NOT NULL,
task_history bigint NOT NULL,
operation smallint NOT NULL,
CONSTRAINT operation_history_pkey PRIMARY KEY (id),
...
0
votes
1answer
31 views
POSTGRES aggregate data from joined table
There is sql-query, running in POSTGRES 9.1. That query uses 24 LEFT JOINs.
Every joined table is able to have from 1 to 10-15 data rows, related to the previous table. As a result of sql-query I have ...
0
votes
0answers
21 views
Call JSON-functions on PostgreSQL 9.1
I've got a query which works on PostgreSQL 9.3, due to the fact that it supports JSON types etc.
I would like to call this query on a remote API, which only runs PostgreSQL 9.1, with no such support.
...
1
vote
1answer
42 views
Postgresql : How do I select top n percent(%) entries from each group/category
We are new to postgres, we have following query by which we can select top N records from each category.
create table temp (
gp char,
val int
);
insert into temp values ('A',10);
...
2
votes
2answers
54 views
PostgreSQL: joining arrays within group by clause
We have a problem grouping arrays into a single array.
We want to join the values from two colums into one single array and aggregate these arrays of multiple rows.
Given the following input:
| id | ...
0
votes
2answers
150 views
How to creating dynamic columns using data PostgreSQL 9.1?
So first off, I am completely new to databases and learning as I go. I have a table that comes from puppetdb in which I use postgresql as the backend. There are approximately 65 to 70 values for each ...
2
votes
1answer
45 views
django: Proper way to recover from IntegrityError
What's the proper way to recover from an IntegrityError, or any other errors that could leave my transactions screwed up without using manual transaction control?
In my application, I'm running into ...
0
votes
1answer
34 views
.execute('INSERT …') does not actually insert data into table
warning: brain fart imminent
I've been at this for a bit now, and I'm guessing this is just a "I'm tired, can't get it through my head" type of issue so I'm hoping you will break this down for me..
...
2
votes
1answer
64 views
Postgres run SQL statement from string
I would like to execute a SQL statement based on a string. I have created the SQL statement through a function which returns a string. Can anybody explain how I can execute the statement that is ...
2
votes
1answer
28 views
postgresql partitioning master table duplicate entries
I ve created a simple partitioning structure as given below:
Master table
CREATE TABLE parent_table
(
id_n numeric(19,0) NOT NULL,
name_v character varying(255),
location_n numeric(19,0),
...
0
votes
1answer
21 views
Translating MySQL query to Postgres
I have this MySQL query which works perfectly for what i need it to do. However, I need to translate this across to a postgres installation, and seem to be having some trouble. The query is as ...
0
votes
2answers
31 views
Display Join Records Data in order of they created in Postgresql and Ruby On Rails
I have three tables in postgresql database:
Members
id
first_name
last_name
created_at
Payments (Like Debit)
id
member_id
amount
created_at
type
Fees (Like Credit)
id
member_id
amount
...
-1
votes
1answer
22 views
PostgreSQL, return columns with invoice counts for year
2 tables
Company
Invoices
I want to return the following
Company Name | invoice_total_2014 | invoice_total_2013
----------------------------------------------------------
Company A | 2000 ...
0
votes
1answer
19 views
Postgresql - COPY FROM value omitted after 64 characters
I'm trying to insert data from a textfile into a Postgres table. However, some data seems to be missing after copying.
The textfile:
...
4
votes
1answer
34 views
How to use join in my case Postgres query ?
i am trying to get product's name and categ_id. I have two tables that are,
product_template
categ_id name
7 Nokia classic
7 Nokia lumia
8 samsung s3
6 ...
0
votes
2answers
42 views
function makepoint postgresql 9.1 error postgis
Migrating postgres 8.4 to 9.1 and moving my project to a new server I get this error
ERROR: function makepoint(unknown, unknown) does not exist LINE 4: ...
0
votes
2answers
40 views
Rails, PG: Query returns one record per distinct attribute value, sorts by association attribute
I have not been having luck writing a Rails query that returns what I'm looking for. Suggested, posted solutions do not allow me to sort by a different attribute than I am selecting for DISTINCT.
In ...
2
votes
1answer
30 views
Insert a .txt file into a a postgresql text field
I have the need to put automatically the content of a .txt files, containing the configuration of web devices, into a field of a table I have defined text type.
I searched on the WEB and I found the ...
4
votes
2answers
77 views
Postgres function returning one record while I have many records?
I have many records which my simple query returning but when i use function it just gives me first record,
firstly i create my own data type using,
CREATE TYPE my_type (usr_id integer , name ...
2
votes
5answers
71 views
How to use regex replace in Postgres function?
I have postgres function in which i am appending values in query such that i have,
DECLARE
clause text = '';
after appending i have some thing like,
clause = "and name='john' and age='24' and ...
2
votes
1answer
48 views
Append Single quotes in Query Postgres Function?
I am trying to append single quote in query in postgres function but result in error please have a look on my postgres function,
CREATE OR REPLACE function test() returns my_type as $$
declare rd ...
2
votes
1answer
46 views
Backing up PostgreSQL Database in Linux
I have to create a postgresql database back up(only the schema) in Linux and restore it on a window machine. I backed up the database with option -E, but I was not able to restore it on the window ...
0
votes
1answer
10 views
Debugging postgresql over pgadmin displays the wrong line
When debugging postgresql over pgadmin, the pgadmin debugger display always a line to low, i.e. if line 10 is going to be executed the marking will stay in line 11.
The problem is that the breaking ...
3
votes
2answers
129 views
How to get unique values from each column based on a condition?
I have been trying to find an optimal solution to select unique values from each column. My problem is I don't know column names in advance since different table has different number of columns. So ...
0
votes
1answer
153 views
PostgreSQL pq Open not successful: x509: certificate signed by unknown authority
What is wrong with this code?
http://godoc.org/github.com/lib/pq
* dbname - The name of the database to connect to
* user - The user to sign in as
* password - The user's password
* host - The host ...
2
votes
1answer
80 views
Create table based on JSON file in postgresSQL
I'm receiving some data in JSON format. I want to store data in database (PSQL 9.1), but structure of data is changing each day.
Data example:
{
"No.":"1",
"Ticker":"A",
"Market Cap":"18468.13",
...
1
vote
1answer
58 views
Postgres: alter table to unlogged
I have seen this answer, How to apply PostgreSQL 9.1 UNLOGGED feature to an existing table?, which basically suggests that the way to convert a table to unlogged is to run:
create unlogged table ...
0
votes
0answers
29 views
Find and delete duplicates?
SELECT
name,
city_id,
street_id,
house_no,
state_id,
country_id,
count(*) as dupl
FROM res_better_zip
GROUP BY
name,
city_id,
street_id,
house_no,
state_id,
...
0
votes
1answer
42 views
Why can't I connect to a Postgres database?
I've installed and started a Postgres 9.1 instance on a DigitalOcean droplet.
When I try to connect to it using my PgAdmin III client (settings see below), I get the message that the server is not ...
0
votes
1answer
40 views
PostgreSQL syntax error related to INSERT and/or WITH. Occurs in 8.4 but not 9.1. Ideas?
Here is some SQL for PostgreSQL (I know it's a silly query; I've boiled the original query down to the simplest broken code):
CREATE TABLE entity (
id SERIAL PRIMARY KEY
);
WITH new_entity
...
0
votes
0answers
82 views
Eclipselink/PostgreSQL table creation fails
Recently I was migrating a database from MySQL to PostgreSQL. I use eclipselink for JPA and have Commons dbcp for connection pooling.
I have configured my persistence.xml to switch the jdbc driver and ...
1
vote
2answers
52 views
Transform long rows to wide, filling all cells
I have long format data on businesses, with a row for each occurrence of a move to a different location, keyed on business id -- there can be several move events for any one business establishment.
...
-2
votes
4answers
50 views
What is the equivalent of varchar type in PostgreSQL?
I'm migrating my MySQL database to PostgreSQL and have a simple question:
What is the best equivalent of varchar(30) in PostgreSQL? Is it text?
3
votes
1answer
63 views
PL/pgsql circular reference function
I have one table with attributes (ID int, SourceID int, TargetID int, TargetType int)
ID SourceID TargetID
---------------------
1 123 456
2 456 789
3 1 123
4 456 ...
0
votes
1answer
44 views
Dblink Insert Update Delete - Number of rows affected always zero
I'm using dblink to do some data manipulation (insert, update or delete) on a remote database. Specifically I am using views, and rules on the views to do the insert, update or delete. For example:
...
2
votes
1answer
63 views
Postgres data encryption for INT column
Say I have table:
Fee
date : DATE
amount : INT
I can't store data in amount column in virgin way, because this is private information, so I need to encrypt it. Also I need to perform arithmetic ...
0
votes
1answer
44 views
Simply by the Query
Table name is group. Column name is groupno,name,grouprefno,detail,undergroupno
Sample data of group
groupno name grouprefno detail undergroupno
1 A 001 abc 0
2 ...
0
votes
1answer
47 views
Postgres failed to create database for rails-composer
I'm running the rails-composer script with
rails new myproject -m https://raw.github.com/RailsApps/rails-composer/master/compser.rb
And everything goes along smoothly until it asks me if I want to ...
0
votes
1answer
83 views
How to handle special characters in the password of a Postgresql URL connection string?
Using a Postgresql URL connection string in the format of:
postgresql://user:secret@localhost
How do I handle special characters in that string (e.g., $) so that it will actually function when I ...
2
votes
1answer
55 views
How can I centralize a complex windowing query used repeatedly but with different partitions each time
I have some range collapsing logic (based on http://wiki.postgresql.org/wiki/Range_aggregation) which I want to re-use over a variety of different column partitions.
Right now I'm accomplishing this ...
0
votes
0answers
38 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
161 views
How to configure/start Jasper Report?
I downloaded and installed Jasper Report server from here (on Linux Ubuntu): http://sourceforge.net/projects/jasperserver/files/latest/download
Its jasperreports-server-cp-5.5.0a version.
I ...
0
votes
0answers
58 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
1answer
42 views
Postgresql stored function returning only values not with column names
I am new in Postgresql. I just created function with composite type.
The following is my code:
create type search_type as (
result_id bigint,
result_name character varying,
result_loc character ...
