Most Popular
1500 questions
35
votes
1 answer
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:
create function f1()
returns integer as
$$
declare
event tablename%ROWTYPE;
....
....
begin
FOR event IN
SELECT * FROM tablename WHERE condition
LOOP
…
skumar
- 361
- 1
- 3
- 7
35
votes
2 answers
Case with multiple conditions
I need to change returned value, from select statement, based on several conditions. I tried something like that:
,CASE i.DocValue
WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
ELSE 0
END as Value
why it is not working and…
Muflix
- 1,099
- 4
- 14
- 26
35
votes
4 answers
why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist
I am trying to run following command:
sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db
I get:
failed: FATAL: database "new_db" does not exist
andilabs
- 627
- 2
- 6
- 11
35
votes
3 answers
psql: FATAL: sorry, too many clients already
I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3.
My database is set to handle 150 maximum connections:
# SHOW max_connections;
…
JohnMerlino
- 1,739
- 5
- 18
- 21
35
votes
2 answers
Improve performance of COUNT/GROUP-BY in large PostgresSQL table?
I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how many nodes I do the following (added explain…
tomka
- 937
- 1
- 9
- 16
35
votes
5 answers
How far should you go with normalization?
I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there performance drawbacks to too much normalization?
Fergus
- 453
- 5
- 7
35
votes
4 answers
How to JOIN two table to get missing rows in the second table
In a simple voting system as
CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),
CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs
for getting the list of elections a user has voted, the…
Googlebot
- 4,521
- 24
- 66
- 95
35
votes
2 answers
ISO Week vs SQL Server Week
Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly according to the ISO standards. I ran this script as a…
Zane
- 3,511
- 3
- 24
- 45
35
votes
7 answers
Display user-defined types and their details
I've created a few new UDTs in PostgreSQL. However, now I have two problems:
how to see which UDTs have been defined?
how to see the columns defined within these UDTs?
Unfortunately, I couldn't find anything on that in the PostgreSQL…
navige
- 599
- 1
- 5
- 8
35
votes
11 answers
How can I get the correct offset between UTC and local times for a date that is before or after DST?
I currently use the following to get a local datetime from a UTC datetime:
SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)
My problem is that if daylight savings time occurs…
Rachel
- 8,477
- 20
- 50
- 74
35
votes
2 answers
In SQL Server, what is the purpose of grouping stored procedures?
One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by calling it usp_DoSomethingAwesome;2.
I discovered this…
swasheck
- 10,665
- 4
- 47
- 88
35
votes
2 answers
If a CTE is defined in a query and is never used, does it make a sound?
Do unused CTEs in queries affect performance and / or alter the generated query plan?
J.D.
- 37,483
- 8
- 54
- 121
35
votes
7 answers
How can I tell if a SQL Server database is still being used?
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum thread which had a T-SQL query you could run to…
jsauni
- 1,040
- 1
- 11
- 15
35
votes
2 answers
Decode Base64 String Natively in SQL Server
I have a varchar column in a table in SQL Server that holds a Base64-encoded text string,
which I would like to decode into its plain text equivalent.
Does SQL Server have any native functionality to handle this type of thing?
Here is a sample…
GWR
- 2,807
- 9
- 33
- 42
35
votes
4 answers
Slower SQL Server performance after allocating more CPU and RAM
We have SQL Server 2008 R2 (10.50.1600) running on a virtual Windows 2008 R2 server. After upgrading the CPU from 1 core to 4 and the RAM from 4 gb to 10 gb, we've noticed the performance is worse.
Some observations I see:
A query that took <5…
Jeff
- 505
- 1
- 4
- 7