You would need to explicitly define the column list, use a filler and then derive it to transform the data type on load:
CREATE TABLE public.test (
datetime TIMESTAMPTZ NOT NULL
);
COPY public.test (unix_timestamp FILLER VARCHAR(15),
datetime AS TO_TIMESTAMP(unix_timestamp))...
The question has been updated but is less clear to me now than before.
Regarding your update, I don't really understand the actual problem you are facing. Is this about your table already having ranges that overlap and you wanting to detect and resolve them? — Andriy M59 secs ago
Tempted to answer that it's just not possible to get Mongo to be faster than Postgres. I'm sure I'll get a gazillion upvotes (for a rather troll answer.) — ypercube1 min ago
@swasheck It's up to you. Part of me would hate to see your creativity destroyed. But another part of me would like to see data-access functions in computed columns destroyed. So I'm conflicted :)
gone. besides, the actual answer is @PaulWhite 's which is why i was asking about the actual delete statement. in the end, we can simply blame it on entity framework
Tempted to answer that it's just not possible to get Mongo to be faster than Postgres. I'm sure I'll get a gazillion upvotes (for a rather troll answer.) — ypercube1 hour ago
What is this guy talking about? I feel I just don't understand something.
Unfortunately it needs to be almost live. Also aggreated results would not be efficient because those logs are not for one user. They are for different user. However I do generate archived reports for reports a user has requested. But they are only valid for 30 minutes. — yoshi27 mins ago
I just got asked to suggest why Azure might not be a great platform for a data warehouse.
- No support for master data services - No system data dictionary (is this for real - not 100% sure from my google-fu) - No sharepoint integration, PowerView etc. - No table partitioning - No standard backup/restore - Requires clustered indexes (not necessarily desirable for fact tables) - Does not support the profiler
That's what a bit of google-fu turns up. Reality check - is that correct?
In SQL Server, log file is used to support transactions, guarantee transaction isolation, and database consistency. If you remember the structure of the log file, you know that it may grow in the following cases:
When there are huge DML (UPDATE, DELETE etc.) activity within
database done by one...
Yep, I may be wrong here. Let me think. I might have confused the isolation mechanics of SQL Server with those of Oracle which uses undo to support isolation.
@YasirArsanukaev If you're thinking of MVCC, SQL Server uses a different mechanism (the version store). In any case, I think the answer would be better without that statement.
Yeah, probably you are right. I just thinking and trying to remember if I remembered everything else correctly and whether it's better to delete than to edit the answer :-D
That doesn't affect logging much though. Undo and redo are still generated as normal in the transaction log even when row versioning is in use. It has to do this to ensure recoverability of the database.
i.e. Row versions are in-memory only, not persisted.
@PaulWhite "Row versions are in-memory only, not persisted" When you said "in memory", you assumed they are temporary (though they go to log file), right?
So that transaction can be rolled back and they are forgotten right afterwards.
Row versions and redo/undo log records are quite separate concepts. Row versions have nothing to do with rollbacks in SQL Server. Also, row versions are not logged (they're temporary, after all).
@mods, I don't know how to flag this. OP asks a question at the DBA site. After an hour (I was just about to comment), they delete they question and ask it (identical) in the SO site. They get an answer there, and we have the discussion (see comments:)
I have a DB of the following structure (picture shows simplified version)
How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe requiredQuaintity > availableQuantity?
I tried this:
SELECT r.Name
FROM
Receipe r
JOIN RecipeIn...
@PaulWhite Yeaah, I know -- until you commit a txn and backup (truncate) the log file, or commit the txn and SQL Server switches over to the beginning of the log file and overwrites over VLFs, afair.
@PaulWhite "Row versions are in-memory only, not persisted" I think I figured what you meant: the versions not go into database backup and are being forgot with time (can't remember the whole mechanics at the moment), so only committed versions are those that persist (in log -- and thus in backups).
I noticed that sometimes my code gets highlighted in different colors when rendered.
What is syntax highlighting?
How does it work?
What if my code isn't highlighted correctly?
How do I report a bug or request a new language?
What languages are currently available on Stack Exchange?
Return to...
For the benefit of future visitors, I'm posting some VB.Net code that allows a process to use NETONLY impersonation to authenticate against a remote server that resides in an untrusted domain:
Option Explicit On
Option Infer Off
Imports System
Imports System.Runtime.InteropServices ' DllImpor...
Oracle on the other hand just copies the current log file when it's full of redo (which can be as big as 54 mebibites) to archive log and switches over to another log (there can be two or more log files) always keeping redo log at the same size (fixed).
@JackDouglas Never shrinks. You just add the redo log file during the database creation or afterwards specifying its size and it always stays at the same size. You may add, drop online redo log files specifying different sizes though. But they never grow or shrink neither manually nor automatically.
@JackDouglas Well, sort of. But what grows is the disk(s) that the full online log file archived (copied online log is called archive log in Oracle) to, not the disk where online log is located.
@JackDouglas @JackDouglas Do you learn Oracle, MySQL, SQLite also? I just noticed (based on your profile) you're predominantly an SQL Server guy (-:
@JackDouglas Yeah. Correct. But something makes me think that planning your LOG_ARCHIVE_DEST_n (or Fast Recovery Area) destination sizes or making more room in them afterwards (when the workload grows) is easier than shrinking log file and adapting your backup strategy in SQL Server. Don't you have the same feeling?
When should I rebuild the indexes in my relational database (e.g. SQL Server)?
You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table.
Is there a case for rebuilding indexes on a regular b...
If you are confident that there will never be a time where you have data from the same week over more than one year, you just need to add the AT TIME ZONE part to the TIMESTAMPTZ type:
CREATE TABLE public.test (
date TIMESTAMPTZ NOT NULL
) PARTITION BY WEEK(date AT TIME ZONE 'UTC');
Oth...
@AndriyM What in the world is she talking about???
> Yes, and what I've been seen saying all along is that your decision to take matters into your own hands and vote for just one of two perfectly good answers is taking the aggregate role of the community and stealing it for yourself!
not sure if I want to post it, same discussion from yesterday....sigh
@AndriyM: Yes, and what I've been seen saying all along is that your decision to take matters into your own hands and vote for just one of two perfectly good answers is taking the aggregate role of the community and stealing it for yourself! The aggregation can't work effectively if everybody is gaming it to promote a single answer - what if everybody is trying to promote a different answer? There's no point in voting at all then because it's all entirely arbitrary. — Lightness Races in Orbit23 mins ago
Ok, strange one here
I have a database for customer data. My customers are businesses with their own customers.
I have 3000 tables (one for each business) with several thousand email addresses in each. Each table is identical, save the name.
I need to find a way to find where emails cross over...
Find the person who designed this monstrosity and punch them in the head. Second tell management this structure has to be changed as it is completely asinine. Third if management refuses then find a new job as this one cannot possibly be paying you enough to put up with this level of incompetence. — Zane47 secs ago
@swasheck We had a discussion at work with a colleague about English names (and other words) with a Greek etymology, like Philip, Alexander, hippopotamus, etc.
He finds it weird that we have so many compound words.
it's idiotic designs like this that feed the ridiculous notion of "JOINS BAD!!! NOSQL GOOOOD!!!" when the RDBMS is a tool in the hands of developers without a clue, then i guess it stands no chance. — swasheck36 secs ago
@Daи i think (and @PaulWhite may have to confirm this), but i think that PIVOT is semantic sugar for your pattern anyway. not sure. could be rong. <---- that's rong <--- so's that
@Lamak Well, I guess it all revolves around her main point that two equally correct answers should be rewarded (voted) equally and anyone who gives preference to one answer over the other is not being honest in their voting.
i need this in a view, but its seems that you cant use declare.
help?
declare @lastsat datetime
set @lastsat =
(select max(fechahoy) from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] where DiaSemana='Saturday')
SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy...
but uhhhh ... there may be some interesting information in the chat transcript here vis a vis as certain person's abstract on the 2014 cardinality estimator
I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 months.
I can use the sp_spaceused statement to calculate the actual size but how do I calculate ev...
I didn't think he would post a question with the username exposed like that. Not that it's too wrong, but in our company that may very well be a cause for termination
@swasheck Put it this way - say we have an AG spread across multiple DCs, in one of our secondary DCs there's two nodes - one of which is an async replica, and the other the server is in the cluster but the DBs are logshipped (so the server's part of the cluster, but the databases technically aren't). If you have all the log backups, you can bring that logshipped copy into the AG, but what happens if you lose a log backup?
i.e. log backup's been taken on the primary, but there's a whole-of-DC outage at the primary before the log backup gets copied to the secondary DC. You can force the AG onto the async replica, but could you then take a log backup (from the formerly-async-replica-now-primary) and use that to replace the missing log on the logshipped copy?