Most Popular
1500 questions
145
votes
7 answers
How to use psql with no password prompt?
I wrote a script to REINDEX indexes in a database. Here is one of them:
echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX…
Majid Azimi
- 2,351
- 3
- 23
- 24
144
votes
15 answers
PostgreSQL not running on Mac
The error in its entirety reads:
psql: could not connect to server: No such file or directory. Is the
server running locally and accepting connections on Unix domain socket
"/tmp/.s.PGSQL.5432"?
This is my second time setting up Postgresql via…
Michael P.
- 2,111
- 2
- 14
- 8
144
votes
8 answers
Why shouldn't we allow NULLs?
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.
All I can seem to think of is that, as an application developer, you…
Thomas Stringer
- 42,434
- 9
- 120
- 155
142
votes
5 answers
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:
Primary: 96GB RAM, 8…
gbn
- 70,237
- 8
- 167
- 244
140
votes
3 answers
Default value for UUID column in Postgres
In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
Basil Bourque
- 11,188
- 20
- 63
- 96
138
votes
5 answers
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor.
I need all of the fields (or nearly…
Joe
- 1,655
- 2
- 11
- 14
137
votes
2 answers
How do I get the current unix timestamp from PostgreSQL?
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL:
This…
Jonas
- 33,945
- 27
- 62
- 64
133
votes
5 answers
MySQL any way to import a huge (32 GB) sql dump faster?
I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:
mysql -uroot dbname < dbname.sql
It is taking too long. There is a table with around 300 million rows, it's…
SBhojani
- 1,433
- 3
- 10
- 4
132
votes
3 answers
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
Additionally, I created an index on A, but Postgres still…
Luciano
- 1,771
- 3
- 12
- 8
131
votes
7 answers
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that…
misterjaytee
- 1,413
- 3
- 12
- 8
130
votes
10 answers
mysql: Show GRANTs for all users
MySQL's SHOW GRANTS shows the permissions of the current user.
Is there a way to log in as root and show the permissions of all users?
Adam Matan
- 12,079
- 30
- 82
- 96
129
votes
5 answers
Best practice between using LEFT JOIN or NOT EXISTS
Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?
What is benefit to using one over the other?
If none, which should be preferred?
SELECT *
FROM tableA A
LEFT JOIN tableB B
ON A.idx = B.idx
WHERE B.idx IS NULL
SELECT…
Michael Richardson
- 1,465
- 2
- 10
- 9
128
votes
4 answers
Difference between Sharding And Replication on MongoDB
I am just confused about how Sharding and Replication work.
According to the definitions I found in the documentation:
Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set.
Sharding: Sharding is a…
Saad Saadi
- 1,411
- 2
- 10
- 7
127
votes
1 answer
Postgres Count with different condition on the same query
EDIT Postgres 9.3
I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2
The current query is working fine which looks like this:
Basically it is a 3 table inner join. I did not make this query but the developer…
jackhammer013
- 1,469
- 2
- 12
- 11
123
votes
3 answers
What is faster, one big query or many small queries?
I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.
So would it be faster…
sudo.ie
- 1,331
- 2
- 9
- 5