Most Popular
1500 questions
22
votes
3 answers
PostgreSQL High Availability/Scalability using HAProxy and PGBouncer
I have multiple PostgreSQL servers for a web application.
Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).
I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432)…
Nicolas Payart
- 2,368
- 5
- 27
- 35
22
votes
2 answers
What is the meaning of tinyint(N)?
When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:
mysql> create table boolean_test (var1 boolean, var2 tinyint);
Query OK, 0 rows affected (0.10 sec)
mysql>…
Cratylus
- 1,003
- 3
- 12
- 18
22
votes
2 answers
Why aren't unsigned integer types available in the top database platforms?
Databases are usually very customizable with varying data types and custom lengths.
It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither PostgreSQL and MS SQL Server. MySQL and Oracle…
Ehryk
- 601
- 1
- 6
- 13
22
votes
3 answers
How to get a list of locked accounts / check that account is locked?
I can use following statement to unlock an account:
ALTER USER username ACCOUNT UNLOCK
But which statement can I use to verify that account is currently locked out?
Mike
- 737
- 8
- 13
- 25
22
votes
2 answers
LIKE uses index, CHARINDEX does not?
This question is related to my old question. The below query was taking 10 to 15 seconds to execute:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE…
IT researcher
- 3,131
- 15
- 57
- 82
22
votes
4 answers
Could not allocate a new page for database because of insufficient disk space
Using the SQL Server Import and Export Wizard, I get this error:
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
Could not allocate a new page for database 'database'…
Jerry Carson
22
votes
6 answers
Don't use a transaction for Stored Procedure
I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.
Is it possible to write…
Matthew Steeples
- 673
- 1
- 7
- 16
22
votes
3 answers
What are the valid formats of a PostgreSQL schema name?
I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot:
start with a number
have spaces
start with pg_
What else? Where should I look?
Ramon Tayag
- 331
- 1
- 2
- 6
22
votes
8 answers
How do I deal with FK constraints when importing data using DTS Import/Export Wizard?
I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY constraint" i have over 40 tables with lots of FK…
user11512
22
votes
1 answer
Database design: Two 1 to many relationships to the same table
I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and Corporation which both can have 0, 1 or many chequing…
dendini
- 395
- 2
- 6
- 13
22
votes
8 answers
Why should an application not use the sa account
My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.
I have an absolute belief that applications should not use the sa password without a…
SQLDBAWithABeard
- 645
- 1
- 6
- 13
22
votes
2 answers
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of differences and the only two of note are:
For indexes on…
Derek Downey
- 23,440
- 11
- 78
- 104
22
votes
7 answers
Where can I find some guidance on index strategies?
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.
As a general rule, which fields should be indexed?
Which fields should not be indexed?
What are the rules for using…
SpecialAgent_W436
- 385
- 1
- 3
- 9
22
votes
2 answers
Export remote Postgres table to CSV file on local machine
I have read-only access to a database on a remote server. So, I can execute:
COPY products TO '/tmp/products.csv' DELIMITER ',';
But on that server I don't have permissions to create/save a file, so I need to do this on my local machine.
When I…
tasmaniski
- 1,175
- 4
- 13
- 16
22
votes
3 answers
What is the correct result for this query?
I came across this puzzle in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server and PostgreSQL return 1 row.
MySQL and Oracle return zero rows.
Which is correct? Or are both equally valid?
Martin Smith
- 84,644
- 15
- 245
- 333