Tagged Questions
0
votes
0answers
34 views
How can I assign a certain php variable into a checkbox value?
I'm trying to make the value attribute of a checkbox a certain value from my SQL database. It needs to print out the list of almost all values, which it currently does. I need to attach be able to add ...
1
vote
1answer
15 views
PostgreSQL - Storing multiple versions of a service description
I am maintaining a web application which allows a user to upload web service descriptions. The service table to store those descriptions looks roughly like this:
CREATE TABLE service(
id integer ...
0
votes
0answers
14 views
Facing Difficulties in arranging complex entity update in PostgreSQL
I have an invoice entity with up to 100 items described by the 3 following tables:
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
name CITEXT NOT NULL UNIQUE CONSTRAINT ...
0
votes
0answers
26 views
counting entries, pooling over columns and returning boolean values
(Feel free to modify the title accordingly)
Hi, I am using Postgres 9.3 on MacOSX. I am also new to any kind of SQL so bare with me. I am trying to create a query function (code and example below), ...
2
votes
3answers
37 views
Rank based on sequence of dates
I am having data as below
**Heading Date**
A 2009-02-01
B 2009-02-03
c 2009-02-05
d 2009-02-06
e 2009-02-08
I need rank as below
Heading Date ...
0
votes
0answers
10 views
django & sql - how can I efficiently store and update sort order information associated with records in my database table?
So I have a Django app and one of the tables is basically a list of items. The user may choose to rearrange the order of this list. When they do this, I want to preserve this information so that the ...
0
votes
0answers
13 views
Doctrine Undefined index: user when joining with view
I have the following models set up with Doctrine in ZF 1.12. The first is from my users table:
/**
*
* @Table(name="users")
* @Entity
*/
class My_Model_User extends My_Model
{
/**
*
* @var ...
0
votes
1answer
28 views
What is the fastest way to compare the minimum right most characters between two fields
I have a phone number field in two different tables. I want to compare the right most characters of both for the minimum length. Is one way more efficient than another to do this in PostgreSQL?
...
1
vote
1answer
29 views
Getting random rows based on column index
As of right now the table setup is like so:
CREATE TABLE testing (
id INT NOT NULL,
text TEXT NOT NULL
);
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(32) NOT NULL
) UNIQUE KEY ...
0
votes
1answer
23 views
How can I return top 4 results by user using Postgres sql
Here is my problem: I have a table that stores the results of surveys taken by users. A single user can take multiple surveys. The key columns are: user_id, the user identifier and survey_id, the ...
3
votes
1answer
33 views
Writing an SQL trigger to find if number appears in column more than X times?
I want to write a Postgres SQL trigger that will basically find if a number appears in a column 5 or more times. If it appears a 5th time, I want to throw an exception. Here is how the table looks:
...
0
votes
0answers
19 views
Design Tips For Database Structure That Takes Sharding Into Consideration [on hold]
I am about to embark on a project that the size of the dataset will increase exponentially and sharding will be required. What are some good database design tips and difficulties should I take in ...
mongodb
0
votes
0answers
12 views
PostreSQL: export column2 to be the contents of a new file, where the filename is column2
I need to pull out some data from a PostgreSQL database - namely some old "lost" functions.
I've got a select statement which pulls up the function name as column1, and the function definition as ...
0
votes
0answers
28 views
ECPG: sizeof() in EXEC SQL DECLARE SECTION
I have Sybase ACE CPRE Embedded SQL С code:
void MyFunc( MyClass*unit_address )
{
EXEC SQL BEGIN DECLARE SECTION;
CS_BINARY var1[sizeof(MyClass)];
EXEC SQL END DECLARE SECTION;
...
0
votes
1answer
33 views
Combine similar data from multiple schemas
I have a table in a common schema which contain details about all enterprises of the application like id, name, logo, web_site_address, schema_name etc. Then there are schemas for each enterprise ...
0
votes
0answers
12 views
npgsql function that returns “create table” “query string”
instead of copying manually, Is there an npgsql or c# method/function that returns "create table" "query string" of an existing table like the one that displays on sql pane of pgAdmin when you ...
-1
votes
1answer
37 views
left join table without effecting calculation left column table?
I have a problem finding a query for my tables, I am using postgresql for my DB
I have 2 tables table A and table B as follow
table A
---------------------------------------------------------------
...
1
vote
2answers
22 views
Postgres geolocation points Distance
I'm trying to make a query to determinate the distances between geolocation points in a Postgres database this is my query
SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - 31.8679), 2) +
...
0
votes
0answers
23 views
Import Excel Data into PostgreSQL 9.3
I've developed a huge table in excel and now facing problem in transferring it into the postgresql database. I've downloaded the odbc software and I'm able to open table created in postgresql with ...
2
votes
3answers
39 views
Recursive/Hierarchical Query Using Postgres
The table: Flight (flight_num, src_city, dest_city, dep_time, arr_time, airfare, mileage)
I need to find the cheapest fare for unlimited stops from any given source city to any given destination ...
0
votes
1answer
12 views
Rails select register from the previous day
I'm trying to get the model instances with one day of history. I'm using postgresSQL.
What i want is likeness this:
element = Element.find(:first , :conditions => ["name LIKE ? AND created_at ...
1
vote
1answer
35 views
Postgresql fastest way of getting the set of unique values in one column or a large table
I have a constantly growing, potentially very large table in a Postgresql database that contains "data" from different "channels" for different "devices" eg.:
Table data:
id (PK)
device_id (FK ...
0
votes
1answer
17 views
Is it possible to access child information from parent in Postgresql Inheritance?
I am creating the DB structure for one site and I am using for First time Table Inheritance. I need to know if there is posibility to catch child fields when I am making selection over the main ...
0
votes
1answer
54 views
how to make one sql query asking for many rows
I have such table (example and only part of it):
node_id | k | v
123 | addr:housenumber | 50
123 | addr:street | Kingsway
123 | addr:city | London
123 | (some other stuff) | .....
...
0
votes
2answers
16 views
Trigger to delete rows from related tables before deleting rows from actual table
I have the following tables:
CREATE TABLE QUESTION(
id varchar(10) NOT NULL PRIMARY KEY,
que_type numeric(1));
CREATE TABLE ESSAY(
que_id varchar(10) NOT NULL PRIMARY KEY,
ans varchar(2000),
FOREIGN ...
0
votes
0answers
23 views
Android, take an entire row from postgreSQL database and put in a table or another structure
i'm new to Android programming but i made a little application that interacts with a postgreSQL database via JDBC e made things that aren't related to my question. My main question is: i have a table ...
android
0
votes
1answer
30 views
How to create insert query across two tables where one table has foreign key in the second table
I have 2 tables:
1> Batch which has id and name columns
2> Trans which has id, batch_id and name columns
Basically for a given batch, there could be multiple Trans records.
I have one Batch record ...
0
votes
1answer
29 views
Deleting related rows from other tables without primary key
I need to delete the associated rows from the "assign" table when a crew member is deleted.
However, when I create the tables, I get the error:
ERROR: there is no unique constraint matching given ...
0
votes
1answer
38 views
PostgreSQL: updating two columns is very slow
The PostgreSQL version is 9.2. The data table contains more than 100 million rows. It has four columns: a, b, c and d. Column a and b are character varying type. I want to add two columns: ...
0
votes
1answer
14 views
Postgres order row for exist field
I have table users and in this table I have column avatar which save path to user avatar. How I can order my query which starts from user which have avatar and after them which heven't.
Example:
...
1
vote
1answer
37 views
postgis query for addresses (with osm data)
I want to make queries for addresses to postgis database with data from openstreetmap, check if such address exist in database and if so, get coordinates. Database was filled from .pbf file using ...
4
votes
2answers
39 views
SQL - find movies with highest number of awards in certain year - code duplication
I am trying to write a query (postgreSql) to get "Movies with highest number of awards in year 2012."
I have following tables:
CREATE TABLE Award(
ID_AWARD bigserial CONSTRAINT Award_pk PRIMARY ...
-4
votes
1answer
34 views
Suggestion needed on SQL query
I have 4 fields like:
Country, Year, Area,Value
USA , 2010, SQL, 78
USA, 2011, C++, 66
UK, 2012, C, 99
Now in this table
3 Areas are there (SQl, C++, C) and 3 different years are there.
So ...
0
votes
1answer
23 views
Hibernate + PostgreSQL + Network Address Type (inet, cdir)
I have started my example project with Hibernate 4.2.7 and PostgreSQL 9.3.1 and everything is going well.
At the moment I would like to use INET PostgreSQL type but I am not able to map it with ...
0
votes
2answers
40 views
Select Distinct On while Order By a different column
I'm coming from a MySQL background, where GROUP BY worked very differently than in Postgres. In Postgres - and apparently any standards-based SQL database - you have to group by all selected columns, ...
1
vote
1answer
28 views
How to efficiently write DISTINCT query in Django with table having foreign keys
I want to show distinct cities of Users in the front end dropdown. For that, i make a db query which fetches distinct city_name from table City but only those cities where users are present.
...
0
votes
1answer
22 views
Obfuscating data in postgres
I'd like to obfuscate data in specific columns in postgres 9.1.
For instance, I want to give all the people a 'random' first and last name.
I can generate a pool of names to use:
select name_first ...
1
vote
2answers
20 views
How to make ORM (ActiveRecord) Models for union queries in Rails
I have an application that has some basic entities
Posts
posts have:
Likes
Comments
and Ratings
I then have an SQL view to query for all three. With that I have a model called something like ...
1
vote
2answers
32 views
SQL Statement to UPDATE three characters in a string
How to take a string from a row in a column named 'link', and modify it by adding three letters to a specific index position in the string.
Specific example:
I want to select the value ...
0
votes
0answers
32 views
Django filter - iregex to match complete word only
So I've been trying to implement a search feature where by a user can enter a keyword and look up users. The search method is supposed to look up exact word matches within the fields of the users e.g ...
0
votes
2answers
28 views
Using Hibernate to CREATE SCHEMA in PostgreSQL
All i want is to execute the following SQL on my PostgreSQL server after my Hibernate SessionFactory has been initialized:
CREATE SCHEMA IF NOT EXISTS "fooschema" AUTHORIZATION "foouser";
Currently ...
1
vote
1answer
25 views
BLOB to Image conversion in PLPGSQL
I am new to PLPGSQL.
I am developing an application where the user will select an image and on submit plpgsql procedure will be called which will save the image on server.
Is there any way to ...
0
votes
1answer
24 views
How to select the key of a group order by column a where a column b is not ordering
is it possible to select the id of the groups ordered accending by column line_to where a column obj_id is not also ordered accending?
So the result for my test-data should be 200 because line_no 2 ...
0
votes
2answers
50 views
SQL ON DELETE CASCADE vs. delete on business logic
Im using postgres with hibernate and i have two sql tables. "TABLE_2" have a manyToOne relationship to "TABLE_1".
CREATE TABLE "TABLE_1"
(
"ID" bigint NOT NULL,
"VALUE" bigint NOT NULL,
...
0
votes
3answers
55 views
Using SQL to convert directed edges into undirected edges
The raw data table is
+--------+--------+--------+
| node_1 | node_2 | weight |
+--------+--------+--------+
| 1 | 2 | 5 |
| 1 | 3 | 10 |
| 2 | 1 | 21 |
| ...
0
votes
1answer
46 views
Add a subquery to SELECT statement that depends on the first column?
Is there a way I can write this query in a way to get the result of my sub query compared with my category_id.
SELECT category_id,
count(id) as TOTAL COUNT,
(select count(*) from products where ...
0
votes
1answer
23 views
Select row by minimal distance
I want to select the closest place to a given longitude, latitude values
so I have created this query
SELECT *
FROM "Places"
WHERE 2 * atan2(
sqrt(
sin((radians(41.647780 - ...
3
votes
0answers
29 views
Slick - Parameters are ignored in a Query Template
I'm trying to write a query template for like so:
val byIdentifier = for {
(identifier, issuer) <- Parameters[(String, String)]
o <- Objects if (o.identifier === identifier) && ...
0
votes
1answer
21 views
Postgres/Postgis - Querying all values in clipped raster
I'm currently using Postgres 9.1
My goal is to clip a PostGIS raster with a polygon. Then I would like either an postgres array or delimited set of values for each of the raster pixels that are ...
1
vote
1answer
47 views
Query execution speed PostgreSQL 8.1 vs PostgreSQL 9.2
My question is general, but I've also aparticulr example to explain my problem. I developed a webapp with persistence layer based on Openjpa and used two databases to test it. One is Postgres 8.1 and ...
