Tagged Questions
0
votes
0answers
23 views
PostgreSQL and general SQL information
Normally, I work in IT/Help Desk type roles, but finding work has been challenging since getting back from the Philippines so I'm taking what I can find. I made some good contact with the technical ...
-5
votes
0answers
25 views
What would the result value be? if the if condition went through or if the else condition when through [on hold]
I would like to know what result would return if I ran this code. I am trying to build a rest api using nodejs, express, and postgresql. Thanks
client.query(if exist(select * from waiter where ...
0
votes
2answers
40 views
If Exist SQL command
Could someone explain the if exist command that is used in SQL?
for example:
if exist(select * from waiter){
update waiter set (...) where waiter_id='somevalue'
} else{
insert into waiter ...
0
votes
2answers
18 views
Find all rows that have a value in a certain column that occurs more than once
Suppose I have a database with two columns, an id (bigint) and text (text). I want to find all rows in the database that have an id that occurs in more than one row. How could I do that?
Example:
...
0
votes
1answer
21 views
How do I delete all records related another record and greater than a certain threshold efficiently?
I'm trying to delete all notifications older than the first 99 for every user. Here's what I'm currently doing in rails:
User.all.each do |u|
u.notifications.order('created_at ...
0
votes
1answer
26 views
Two client queries in that same function
I have a question about if it is right to do two client.queries one right after the other. I am trying to build a REST API. Example:
client.query("select waiters_id from waiters", function(err, ...
1
vote
1answer
10 views
How to work with custom SGBD types with jOOQ
I'm wondering how to leverage the new range types you can find in the latests versions of PostgresSQL.
I can find the jOOQ documentation on how to manage normal column with custom Java types, ...
0
votes
1answer
20 views
postgres: Delete rows that has foreign key while not deleting data of the foreign key table
I have postgres database, I my application is build on using django and I used south migration to maintain the database schema. I have following scenario:
user_table has a foreign key relationship ...
0
votes
1answer
31 views
PostgreSQL query for multiple update
I have a table in which I have 4 columns: emp_no,desig_name,from_date and to_date:
emp_no desig_name from_date to_date
1001 engineer 2004-08-01 00:00:00
1001 ...
0
votes
1answer
9 views
connecting a remote server database from a database funcion in postgresql9.1
Friends
i just want to know is it possible to connect another server database which is in same lan network from a store function of another database in postgresql 9.1
example
server a have a database ...
1
vote
0answers
36 views
Duplicate values present in primary key column in postgresql
I have a table in which one of the columns is primary key and it is of data type citext.
Many threads will update the table, they are properly synchronized. Those threads write the data in ...
0
votes
2answers
30 views
Between use two years in postgresql
I want to delete the data between some years, like Table name is t1, Columns id,name date.
The values like this
+--+----+----------+
|id|name|date |
+--+----+----------+
|1 |Raj |2013-03-01|
...
0
votes
0answers
31 views
calculate total minutes between two timestamps across multiple records using postgresql
I have a postgresql 9.1 table with 2 timestamps that record time periods that may overlap. Example:
ID Start End
1 2014-01-19 21:14:59+00 2014-01-19 21:30:00+00
1 ...
0
votes
1answer
28 views
PostgreSQL, one-to-many query, without duplicating the one?
Is there a good way to return, in a single query, a one-to-many relation, without sending duplicates of the fields for the tuple on the one side? Say you have two tables with a typical 1-to-many ...
0
votes
1answer
19 views
Postgres Fill In Empty Data When Aggregating
In Postgres I am aggregating a total amount, separating days, and then dumping that data into chart. Example query looks like this:
SELECT
EXTRACT(YEAR FROM post_created_date) as report_year, ...
0
votes
0answers
33 views
NodeJS hangs indefinitely after multiple requests
I have a nodeJS(v.0.10.23) proxy connecting to a postgres db (node-postgres module v2.1.0) which returns all sorts of json data.
Back in the day, this is how connection errors were handled:
var ...
0
votes
1answer
38 views
Use DEFAULT value if empty string
How can I tell Postgres that a column with an empty string should use the DEFAULT value?
Using CHECK doesn't seem to work here.
1
vote
1answer
56 views
Average of two columns in SQL
I wanted to take average of two columns and display in a new column something like this:
+-+--+--+--+--------+--------+
|A|B |C |D |AVG(B/C)|AVG(C/B)|
+-+--+--+--+--------+--------+
|S|23|34|56| ...
0
votes
2answers
63 views
What are the difference between these two SQL queries?
What are the difference between two sql querries?
1)
select mytab.name, mytab.age, films.title, films.author
from films, mytab
where films.id = mytab.id;
2)
select mytab.name, mytab.age, ...
0
votes
3answers
21 views
How to add OR to a where statement when setting Rails instance variable?
How would I add "OR" to this where statement when setting my instance variable in a Rails controller?
@activities = PublicActivity::Activity.order("created_at DESC").where(owner_type: "User", ...
0
votes
1answer
17 views
Conditional joining in Postgres using levenshtein
I have two tables lets say Table A and Table B...
I want to query these two tables so that I can check to see if two columns in the tables say col1 and col2 are simplier and show them.
Something ...
0
votes
2answers
22 views
Select SQL Inner Join and Omit Certain Record
I have two tables with following data:
Table A
ID DESC
1 One
2 Two
3 Three
ID is primary key
Table B shows the action I did to ID in table A
NO ACTION ID ...
0
votes
2answers
43 views
PostgreSQL query where date oldest
I have a table in Postgres called calendar and I am trying to query the first date value stored where year=2013.
I have written the following query which works, however I am wondering if I can query ...
0
votes
4answers
33 views
postgres column with nulll values and and filtering with “!=”
Here is my query:
select * from students where status != 4.
I am not getting rows with status null.
Shouldn't it return all the rows where status is not 4 - including rows with null status ...
1
vote
1answer
30 views
Nearest Value Between Tables
I have a relation:
CREATE TABLE mag (
pg_id serial,
nt numeric,
time numeric
);
Containing a few tuples:
INSERT INTO mag
(nt, time)
VALUES (10,100), (11,200), (12,300), (13,400), (14,500);
I ...
0
votes
3answers
25 views
PostgreSQL get time difference between timestamps
I've looked at many SO questions related to this but I can't seem to get anything to work. I'm not very good with semi complex SQL queries.
I want to get the difference between the current time and ...
0
votes
0answers
32 views
Posgresql: Return a single row with window function
I have a data set that has two level breakdown; trying to summarize over the top level with a window function (a cumulative sum) but window functions does not group output into a single row.
The ...
1
vote
2answers
38 views
Remove duplicated rows in sql
I want to remove duplicated rows in sql. My table looks like that:
CREATE TABLE test_table
(
id Serial,
Date Date,
Time Time,
Open double precision,
High double precision,
Low double precision
);
...
1
vote
1answer
26 views
django postgresql query not working
I have a postgreSQL database that has a table foo that I've created outside of django. I used manage.py inspectdb to build the model for table foo for me. This technique worked fine when I was using ...
0
votes
1answer
11 views
Importing a csv file into postgresql
I have a *.csv file which I want to import into my database. basically it looks like that:
2013.11.07,11:50,1.35163,1.35167,1.35161,1.35163,15
2013.11.07,11:51,1.35166,1.35173,1.35165,1.35170,21
...
0
votes
0answers
27 views
best practices for maintaining consistent set of column types in SQL data definition language [on hold]
I would like to have a consistent set of column data types in my SQL DDL scripts.
E.g. things like "person first names are VARCHAR(50)", "URLs are VARCHAR(2000)" and so on.
I am looking for something ...
0
votes
1answer
19 views
two similar pg_query statements, one is OK, the other FAILS. Why?
I am having a confusion on what is happening. So I just need some ideas or clarifications.
I have 3 PHP variables and two PostgreSQL queries:
$date_start = $d1 = "2013-01-01";
$date_end = $d2 = ...
0
votes
1answer
53 views
How to get table name from current query in php or postgresql
is it possible to get table name from current query using pdo php or normal sql in postgresql?
In pdo php a function exists like PDOStatement::getColumnMeta but this function doesn't work well, ...
2
votes
1answer
25 views
Why isn't Postgres using the index?
I have a table with an integer column called account_id. I have an index on that column.
But seems Postgres doesn't want to use my index:
EXPLAIN ANALYZE SELECT "invoices".* FROM "invoices" WHERE ...
1
vote
1answer
36 views
Writing data flow to postgresql
I know that by doing:
COPY test FROM '/path/to/csv/example.txt' DELIMITER ',' CSV;
I can import csv data to postgresql.
However, I do not have a static csv file. My csv file gets downloaded ...
0
votes
2answers
39 views
sql left outer join with a constraining column
Here is the SQL, 'aal_county_zip' has entry for 2 zipcodes whereas 'us_zip' has 15 zipcodes. The requirement is to get 15 rows with only 2 rows having data from 'aal_county_zip'. It works like a ...
0
votes
1answer
16 views
PostgreSQL Select where multiple OR statements
I have the following PgSQL query, in which i'm looking where user_id equals any one of a set of id's i am looking for.
Question is, is there a way to simply the statement so that I dont have to keep ...
0
votes
1answer
41 views
Why doesn't Postgresql use index for IN query?
I have a table social_accounts with a partial index on column facebook_id where user_id IS NULL.
If I do a simple query WHERE facebook_id = '123', the index is used:
=> EXPLAIN for: SELECT ...
0
votes
1answer
33 views
SQL query writing help - PostgreSQL
I am not a proficient SQL query writer. I can handle a few table joins. Pretty novice. Seek some advise and help.
DB = Postgres
This looks trivial, but, I am unaware of how to proceed this with the ...
0
votes
1answer
17 views
writing “assertion” views - union of select count(*) subqueries
I am not sure what would be an appropriate title for this question.
Anyway, I have a rather complex SQL schema and a number of views built on top of it.
To ensure that views are correct I maintain ...
0
votes
1answer
28 views
Convert a complex PostgreSQL query to Ruby
I'm new here and have some difficult to convert a complex Postgres query to Ruby. Here is the query:
SELECT date_part('year', p.created_at) AS anio,
date_part('month'::text, p.created_at) AS mes,
...
0
votes
3answers
28 views
group by and SUM only rows satisfying a criterion, but show the others too
I have two tables:
subject which holds a list of subjects and their credits
exams which shows which subjects the students failed or succeeded in the exams
I am trying to get a list of total ...
-1
votes
2answers
15 views
Postgre SQL Schema Bind of Connection
we have a presentation of a java gui handling a postgresql database.
On the presentation-server we have some schemas and one is reserved for our group.
My problem now is in binding a Schema to the ...
2
votes
1answer
35 views
Inner join order does matter in my query
First Query
select name, letters, date, count(p.playernumber) as data
from players p inner join fines f using(playernumber)
group by playernumber
Second Query
select name, letters, date, ...
3
votes
2answers
24 views
Filtering on a value computed using a subquery
This query works :
select r.id, name, description, private, auth,
(select count (*) from message m where m.room = r.id) as messageCount
from room r left join room_auth a on a.room=r.id and ...
0
votes
1answer
23 views
Get value from resultset in an array
I am using postgresql. I am executing a query with some rows
queryObj = session.createSQLQuery(query);
I have an object say Object[ ] row = null;
The query gives me a table consisting of 5 ...
0
votes
2answers
34 views
postgresql distinct not working
I am using the following code for getting value from the database:
But when i wrote this code (testing as to see where the problem is..) i noticed the query is not fetching the distinct value out ...
0
votes
1answer
20 views
Will transaction isolation level resolve race condition in multi-threaded webapp?
I'm a bit confused what to do, I have a web application which handles incoming requests in multi-threaded way (so that is almost certain some requests are processed simultaneously).
Now there is a ...
0
votes
1answer
26 views
How reliable is the cost measurement in PostgreSQL Explain Plan?
The queries are performed on a large table with 11 million rows. I have already performed an ANALYZE on the table prior to the query executions.
Query 1:
SELECT *
FROM accounts t1
LEFT OUTER JOIN ...
1
vote
2answers
42 views
postgresql: LIMIT for each IN clause
SELECT children.*
FROM
children
JOIN parents ON (children.parent_id = parents.id)
WHERE
parents.id IN (1,2,3,4,5)
ORDER BY children.age DESC
LIMIT 1
This will limit the result to 1 for the whole ...
