Most Popular
1500 questions
21
votes
5 answers
Select multiple values in LIKE Operator
I have a SQL query given below, I want to select multiple value using like operator.
Is my Query correct?
SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM employee
INNER JOIN employee_mdata_history
ON …
l.lijith
- 898
- 4
- 9
- 26
21
votes
2 answers
Dealing with disk space full in postgresql
I have a Django web application with postgresql 9.3.10 backend (sitting in a Linux OS). I ran into disk full error, such that even if I try to truncate a table, I get errors of the sort:
ERROR: could not extend file "base/30137/33186048": No space…
Hassan Baig
- 1,959
- 8
- 29
- 41
21
votes
2 answers
What index to use with lots of duplicate values?
Let's make a few assumptions:
I have table that looks like this:
a | b
---+---
a | -1
a | 17
...
a | 21
c | 17
c | -3
...
c | 22
Facts about my set:
Size of the whole table is ~ 1010 rows.
I have ~ 100k rows with value a in column a,…
foo
- 313
- 1
- 2
- 6
21
votes
2 answers
Deferrable unique index in postgres
Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in).
Indexes can also be associated with a constraint, as long as:
The…
jcristovao
- 333
- 1
- 2
- 5
21
votes
6 answers
How to create a row for every day in a date range using a stored procedure?
I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user.
So, let's say I have a table…
Rob V
- 375
- 2
- 4
- 8
21
votes
5 answers
What standard should I follow when naming tables and views?
What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some way like ct_, lut_, or codes_? Are there any other…
Beth Lang
- 952
- 1
- 9
- 19
21
votes
3 answers
Understanding how to choose between fields and tags in InfluxDB
What are some good rules and examples of how to choose between storing data in fields vs. tags when designing InfluxDB schemas?
What I've found so far is:
a measurement that changes over time should be a field, and metadata about the measurement…
Dan Dascalescu
- 311
- 1
- 2
- 7
21
votes
1 answer
Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?
In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good…
joanolo
- 13,397
- 7
- 36
- 65
21
votes
1 answer
Performance comparison between using Join and Window function to get lead and lag values
I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a specific id.
I have used two methods to achieve…
Jason
- 683
- 1
- 12
- 26
21
votes
3 answers
Wrapping query in IF EXISTS makes it very slow
I have the below query :
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
The above query completes in three seconds.
If the…
TheGameiswar
- 2,989
- 4
- 29
- 50
21
votes
3 answers
Why did Postgres UPDATE take 39 hours?
I have a Postgres table with ~2.1 million rows. I ran the below update on it:
WITH stops AS (
SELECT id,
rank() OVER (ORDER BY offense_timestamp,
defendant_dl,
offense_street_number,
…
Aren Cambre
- 355
- 1
- 3
- 7
21
votes
1 answer
What are the best practices for running SQL Server in a Virtual Machine?
What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing reporting data to multiple web sites.
JerryOL
- 325
- 1
- 3
- 7
21
votes
3 answers
MySQL - Delete row that has a foreign key constraint which reference to itself
I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model.
The following is a simplified structure of the table:
Id (PRIMARY KEY)
Owner_Id…
Alon Eitan
- 349
- 1
- 4
- 13
21
votes
2 answers
Non-integer primary key considerations
Context
I'm designing a database (on PostgreSQL 9.6) which will store data from a distributed application. Due to the application's distributed nature, I can not use auto-increment integers (SERIAL) as my primary key because of potential…
Renato Massaro
- 319
- 2
- 5
21
votes
5 answers
Why is this explicit cast causing problems only with a Linked Server?
I am querying data from a linked server through a view on the origin server.
The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable…
krystah
- 747
- 8
- 19