Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
69
votes
3 answers

Can't rename columns in PostgreSQL views with CREATE OR REPLACE

In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names. This works CREATE OR REPLACE VIEW gfam.nice_builds AS SELECT (family_tree.family_tree_id) as x, …
Aleksandr Levchuk
  • 1,227
  • 1
  • 10
  • 11
69
votes
1 answer

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new and swap them when computation is ready. I could…
Ben
  • 887
  • 1
  • 7
  • 9
69
votes
4 answers

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the method below, which at least shows me movement…
Jonesome Reinstate Monica
  • 3,489
  • 10
  • 40
  • 57
68
votes
4 answers

How to run recurring tasks on Postgresql without an external cron-like tool?

I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent. Do you know of an "internal" alternative which…
Stephan
  • 1,513
  • 4
  • 18
  • 27
68
votes
8 answers

What is the difference between a "record" and a "row" in SQL Server?

There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate. So how do we differentiate between these related terms and how we use them properly? Row Record
swasheck
  • 10,755
  • 5
  • 48
  • 89
68
votes
7 answers

What are the differences between "Stored Procedures" and "Stored Functions"?

So a comment to this question mentions, that there is a slight difference between "Stored Procedures" and "Stored Functions" in PostgreSQL. The comment links to a Wikipedia article but some of this don't seem to apply (e.g. that they can be used in…
DrColossos
  • 7,447
  • 2
  • 33
  • 30
68
votes
5 answers

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
Toby
  • 1,128
  • 2
  • 12
  • 12
68
votes
3 answers

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
Lizardie
  • 813
  • 1
  • 6
  • 8
67
votes
3 answers

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the trouble of migrating the database to convert it to a…
Jason Baker
  • 771
  • 1
  • 5
  • 6
67
votes
3 answers

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a program that I could use that does not suck?
Alexis K
  • 789
  • 1
  • 5
  • 5
67
votes
1 answer

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation…
Paul White
  • 94,921
  • 30
  • 437
  • 687
67
votes
6 answers

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses…
sudo
  • 1,061
  • 1
  • 10
  • 18
66
votes
3 answers

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I have a self-referencing table with a primary key, id…
David LeBauer
  • 3,162
  • 8
  • 32
  • 34
66
votes
4 answers

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last…
Juan Velez
  • 3,303
  • 19
  • 57
  • 75
66
votes
2 answers

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
Peter Groves
  • 1,165
  • 2
  • 9
  • 7
1 2 3
…
99 100