Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
20
votes
2 answers

Write a slow query to test slow query logging?

Is there a simple query that would take > 2 sec so that I can test the slow query logger? I am looking for something like a generic recursive or iterative statement.
David LeBauer
  • 3,162
  • 8
  • 32
  • 34
20
votes
1 answer

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last added column fails with a ERROR: cannot drop…
Yanick Rochon
  • 1,651
  • 4
  • 20
  • 28
20
votes
4 answers

Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need to comprise all columns. Queries against the…
goric
  • 1,646
  • 3
  • 18
  • 25
20
votes
1 answer

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. Some of the fields that each user has will be the…
tptcat
  • 303
  • 2
  • 3
  • 6
20
votes
2 answers

Postgresql: set default psql client encoding

When I connect to a Postgresql DB using psql, I often get these messages: => SELECT * FROM question_view ; ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in encoding "LATIN1" Following this SO answer, I…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
20
votes
6 answers

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a…
user39509
20
votes
3 answers

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking about database access permissions here, but…
JohnCand
  • 525
  • 1
  • 5
  • 10
20
votes
1 answer

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) DEFAULT '' NOT NULL, PRIMARY KEY ("id") ); Later…
Valentin Despa
  • 395
  • 1
  • 3
  • 9
20
votes
3 answers

Implications of changing compatibility mode in SQL Server from 100 to 110

I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012. I was trying to execute below query to calculate percentile select Distinct [KEY],PERCENTILE_CONT(0.25) within group(order by EachPrice) OVER(Partition…
Shiva
  • 807
  • 3
  • 8
  • 17
20
votes
3 answers

Set value of character_set_client to utf8mb4

I'm trying to convert my DB to utf8mb4 following this guide. I have set: [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] init-connect='SET NAMES…
qwaz
  • 305
  • 1
  • 2
  • 7
20
votes
4 answers

Is "CREATE INDEX` in MySQL a Linear Operation?

What I mean is the following: If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time. What I'm trying to achieve is to to estimate the time it takes to create the…
Nifle
  • 1,472
  • 8
  • 17
  • 31
20
votes
4 answers

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small neighborhood (200 houses) but eventually growing to…
Schmitty23
  • 301
  • 1
  • 2
  • 4
20
votes
5 answers

What is the exact relationship between a database transaction and locking?

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response. As a long-time application developer, I know at some level what a transaction is (I use them all the time). Leaving aside transaction…
Laird Nelson
  • 303
  • 2
  • 6
20
votes
2 answers

In SQL Server, how do read locks work?

Suppose I have the following long-running query UPDATE [Table1] SET [Col1] = 'some value' WHERE [Col2] -- some clause which selects thousands of rows and suppose the following query is executed while the above query is running SELECT * FROM…
cm007
  • 373
  • 2
  • 3
  • 6
20
votes
6 answers

How do I find out if a procedure or function exists in a mysql database?

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
xenoterracide
  • 2,921
  • 5
  • 31
  • 33
1 2 3
…
99 100