Tagged Questions
0
votes
3answers
36 views
How to “analyze” a SQL query?
I'm not sure if I used the right term in my question.
I am creating a prototype, which allows users to type in a raw SQL query, which will query the backend DB. I want to also make sure that users ...
0
votes
1answer
52 views
Need an idea for a complex SQL question
I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-)
Here is what I am trying to accomplish (for PostgreSQL):
Got a ...
0
votes
1answer
26 views
Date series in PostgreSQL [closed]
I want a series in PostgreSQL which gives the date of every week interval before that date.
Example
Date Selected is 05/31/2013, so the series must be:
05/31/2013
05/24/2013
05/17/2013
...
3
votes
1answer
106 views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
2
votes
1answer
35 views
How to select nodes where all children is satisfied?
I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.
...
0
votes
1answer
71 views
pgAdmin3 can't connect properly to Postgres 9.2
I have installed pgadmin3 version 1.10.2, however when it connects to PostgreSQL server version 9.2, it says:
Warning:
This version of pgAdmin has only been tested with PostgreSQL version
...
1
vote
2answers
59 views
Join on different types
In a database I have two tables:
The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying
The two tables are related: if it exists a ...
0
votes
1answer
41 views
Upload (someone else's data) to Postgres, SQL Server [closed]
I work for a company that does program evaluations for state agencies. Thus, I frequently work with someone else's data. They send me the data via sftp:/, mail, etc. and I load it into either SQL ...
2
votes
2answers
57 views
Postgres 9.2 select multiple specific rows in one query
I have a table with three columns,
Id Serial.
Value real.
timein Timestamp.
I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
1
vote
1answer
33 views
Moving postgresql data directory
I moved postgresql's data directory by following the following steps:
Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data ...
1
vote
0answers
49 views
How to Change location of postgres cluster and database within the same machine? [duplicate]
I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is ...
0
votes
1answer
57 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
0
votes
1answer
41 views
String replace using concatenated strings from various columns
I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.
The specification says:
...
0
votes
1answer
31 views
PostgreSQL created a PostgreSQL user on mac. Is this necessary?
I just installed PostgreSQL on a Mac (Mountain Lion) today.
The installer created a mac user PostgreSQL on my machine. After installation, the user still exists.
My question is that why postgreSQL ...
4
votes
1answer
38 views
How to view the full, flattened query underlying a Postgresql view?
If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
0
votes
1answer
100 views
Select first row (grouping) + add aggregate function
First have a look at this question on StackOverflow.
I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.
How can I combine the ...
2
votes
1answer
116 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
1
vote
2answers
84 views
Creating a PostgreSQL SERIAL column using pgAdmin3
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?
Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
2answers
52 views
“Rewinding” a Postgresql database
I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
0
votes
2answers
81 views
Storing data in PostgreSQL: One table or two?
I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.
Question: Because every product can have different ...
0
votes
0answers
22 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
0
votes
2answers
109 views
Use CASE to select columns in UPDATE query?
I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:
SELECT CASE WHEN val = 0 THEN column_x
WHEN val = 1 THEN column_y
ELSE 0
END AS ...
0
votes
1answer
27 views
Comparing two tables for a UUID change and fix it
I have two tables which I'm trying to reconcile the differences of in postgresql.
Table A is old and needs updating.
Table B is an updated, schema identical version of Table A which I have the data ...
0
votes
1answer
46 views
Set field values to newly imported rows in PostgreSQL table with existing data
I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.
Question: For the newly imported ...
1
vote
1answer
25 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
2
votes
1answer
80 views
Limiting number of results in a Partition using OVER(PARTITION BY)
In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
130 views
How to use array variable in query in PostgreSQL
Create table t1 ( xcheck varchar[], name text );
CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
RETURNS record AS
DECLARE xrc as record;
execute 'select name from t1 where xcheck @> ...
2
votes
1answer
39 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
2
votes
1answer
105 views
How to make postgresql SUM to be more accurate on large amount of floating point data?
I'm trying to SUM 8 million floating point (REAL) values with simple query like this:
SELECT SUM(metric) FROM metrics;
However, it returns very inaccurate result.
It should return 137,586.77, but ...
2
votes
2answers
75 views
Postgres RIGHT JOIN with custom array
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:
SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)
...
0
votes
0answers
173 views
Achieve PostgreSQL hstore functionality in MySQL
Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table.
I don't know if this feature is available out of the box in MySQL like ...
3
votes
2answers
136 views
pessimistic locking and client death
We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
3
votes
1answer
69 views
Avoiding repetition without creating a view
Suppose that I have a query Q1 and I need to run a query like the following:
Q1
union
select *
from (some query that uses Q1 outcome)
I would like to do that:
Without creating any view
Without ...
2
votes
1answer
73 views
How to Model / Quickly test for set membership in SQL?
Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below
Desired Table Structure
pkey | data | roles
...
6
votes
6answers
484 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
2
votes
2answers
395 views
How do I remove duplicate records in a join table in PostgreSQL?
I have a table that has a schema like this:
create_table "questions_tags", :id => false, :force => true do |t|
t.integer "question_id"
t.integer "tag_id"
end
...
1
vote
0answers
161 views
Is there a Postgres admin GUI that can execute a master file containing multiple sql files
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.
example: ...
1
vote
1answer
45 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
94 views
How to Reorganize Microsoft Access Table
I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
2
votes
0answers
40 views
What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?
We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
5
votes
1answer
143 views
How to improve performance on PostgreSQL when using multiple concurrent processes?
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
0
votes
0answers
193 views
Postgres error ( XX000: invalid memory alloc request size 4294967290 )
I'm getting the following error in my postgres 8.4 database:
XX000: invalid memory alloc request size 4294967290
Operating System : Windows 7 64 bit
Memory : 8GB
Database Version: Postgres 8.4 ...
4
votes
3answers
91 views
Filtering data that could have more than one category
I have a table like the following:
Annotation (
document,
term,
category
)
Where document and term are some ID, while category is an integer.
The couple document - term is not unique, ...
0
votes
1answer
48 views
Faster joining on 'most recent' related rows
I have a bug tracking application. The bug table stores very little,
because every state change to a bug happens by adding a comment. For
example, the current state of a bug is determined by the state ...
4
votes
2answers
127 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
0
votes
1answer
44 views
Postgresql: Return Unique Rows
I'm using postgresql and I have a large database. A simplified version of the datastructure for my table looks like this:
id (primary unique key), data, userID
1, foo, 4
2, foobar, 4
3, lala, 5
4, ...
0
votes
1answer
89 views
Permission Denied Copy [closed]
I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS.
copy tname from '/Users/Shared/folder/file.cxv' ...
0
votes
0answers
60 views
Monitoring calculation in xDB replication server in PostgreSQL
I am using PostgreSQL 9.2.
I have installed xDB replication server through stack builder.
I want to know the how much the server slave is lag behind the master.
Master - 192.168.1.2
Slave - ...
1
vote
1answer
160 views
Efficient query plan for selecting all data duplicated on several columns
The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well.
The purpose of the query is to show all ...
3
votes
1answer
86 views
Aggregating statistics from child rows
I have a parent row and then many child rows, and I must aggregate statistics from these child rows.
For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
