Highest scored questions

1526 votes

9 answers

3.1m views

How do I list all databases and tables using psql?

When I log in with psql --username=postgres, how do I list all databases and tables? I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, ...

Jonas

32.4k

asked Feb 17, 2011 at 8:45

537 votes

4 answers

1.0m views

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?

markdorison

5,505

asked Dec 16, 2011 at 20:39

529 votes

5 answers

1.1m views

List all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with ...

Stephane Rolland

8,351

asked Aug 13, 2012 at 12:44

482 votes

2 answers

254k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?

Martin Smith

83.1k

asked Apr 11, 2012 at 22:22

414 votes

5 answers

774k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;

Stéphane

5,295

asked Apr 15, 2013 at 19:19

382 votes

4 answers

875k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...

Jonas

32.4k

asked May 27, 2011 at 8:33

353 votes

12 answers

1.1m views

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", ...

Jin

3,643

asked Dec 1, 2014 at 10:54

340 votes

3 answers

211k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?

Jonas

32.4k

asked Jun 3, 2011 at 10:22

302 votes

15 answers

192k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...

haxney

2,493

asked Jul 2, 2012 at 19:36

297 votes

4 answers

344k views

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log ...

Mike Walsh

18.1k

asked Dec 5, 2012 at 2:11

294 votes

10 answers

931k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...

pedrosanta

3,253

asked Aug 3, 2011 at 19:01

294 votes

18 answers

289k views

How can I monitor the progress of an import of a large .sql file?

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib 5.0....

qazwsx

3,427

asked May 2, 2012 at 16:54

280 votes

10 answers

284k views

What are the main differences between InnoDB and MyISAM?

ilhan

3,029

asked Jan 3, 2011 at 20:46

272 votes

7 answers

144k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...

bernd_k

12.1k

asked Jan 4, 2011 at 10:32

253 votes

6 answers

440k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...

alfish

2,934

asked Oct 21, 2012 at 15:03

243 votes

18 answers

120k views

How do you document your databases?

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using. ...

Community wiki

4 revs, 2 users 72%
user316

236 votes

6 answers

375k views

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I ...

Alex

2,555

asked Jan 30, 2012 at 11:28

223 votes

3 answers

242k views

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?

GSto

2,333

asked Jan 21, 2013 at 20:28

215 votes

2 answers

543k views

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.

user3397998

2,261

asked Sep 17, 2014 at 3:13

211 votes

4 answers

233k views

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql , but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) ...

subhanshu kumar

asked Jul 30, 2020 at 16:54

207 votes

7 answers

291k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...

Patrick

4,229

asked Jan 3, 2011 at 21:13

206 votes

5 answers

547k views

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all ...

PlaidFan

2,165

asked Feb 1, 2013 at 3:27

206 votes

6 answers

155k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed ...

Stuart Blackler

4,512

asked Nov 10, 2011 at 21:31

197 votes

17 answers

313k views

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. ...

randomguy

2,111

asked Feb 20, 2011 at 12:10

193 votes

5 answers

271k views

How to get the name of the current database from within PostgreSQL?

Using \c <database_name> in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: ...

Amelio Vazquez-Reina

2,055

asked Feb 5, 2014 at 21:16

188 votes

7 answers

212k views

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, ...

Rachel

8,417

asked Feb 15, 2012 at 16:47

188 votes

12 answers

244k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -...

Adam Matan

11.4k

asked Mar 29, 2011 at 8:19

174 votes

19 answers

60k views

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...

Tom Hunter

2,139

asked Jan 6, 2012 at 15:57

172 votes

4 answers

177k views

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

Any suggestions on how to deal with this error: TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit ...

Jonathan Allen

3,542

asked Nov 3, 2017 at 23:29

170 votes

6 answers

161k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...

BrunoLM

3,393

asked Jan 5, 2011 at 7:46

168 votes

13 answers

334k views

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, ...

John

1,841

asked Jan 4, 2011 at 15:38

166 votes

12 answers

111k views

Should binary files be stored in the database?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...

Jack Douglas

39.4k

asked Apr 29, 2011 at 12:02

162 votes

6 answers

311k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...

Johntron

1,753

asked Jun 20, 2013 at 14:18

156 votes

12 answers

434k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?

user2645263

asked Oct 22, 2013 at 12:26

153 votes

4 answers

240k views

How to list all constraints of a table in PostgreSQL?

How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?

Thirumal

2,298

asked Aug 14, 2018 at 7:32

150 votes

3 answers

164k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...

Manuel Leduc

1,611

asked Dec 27, 2011 at 9:10

149 votes

9 answers

105k views

How to properly format sqlite shell output?

If I go to mysql shell and type SELECT * FROM users I get - +--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+ | USERID | ...

Kshitiz Sharma

3,187

asked Apr 23, 2013 at 5:24

147 votes

3 answers

82k views

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to ...

Jake Wilson

2,417

asked Oct 19, 2011 at 15:50

146 votes

4 answers

232k views

Is it safe to delete mysql-bin files?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...

user18530

asked Apr 26, 2013 at 20:56

144 votes

4 answers

159k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...

Stephane Rolland

8,351

asked Apr 9, 2013 at 19:07

143 votes

8 answers

228k views

Pattern matching with LIKE, SIMILAR TO or regular expressions

I had to write a simple query where I go looking for people's name that start with a B or a D: SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if ...

Lucas Kauffman

1,775

asked Jan 15, 2012 at 11:24

142 votes

5 answers

248k views

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/...

gbn

69.6k

asked Sep 12, 2011 at 14:59

141 votes

8 answers

72k views

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 ...

Thomas Stringer

42k

asked Aug 30, 2011 at 21:11

139 votes

1 answer

160k views

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...

vastlysuperiorman

1,555

asked Jul 1, 2014 at 16:54

138 votes

15 answers

339k views

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"?...

Michael P.

2,051

asked Aug 28, 2014 at 14:50

138 votes

11 answers

258k views

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to utf8mb4_bin....

geoaxis

1,717

asked Nov 22, 2011 at 12:18

136 votes

5 answers

150k views

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 ...

Joe

1,621

asked Sep 7, 2012 at 9:39

136 votes

3 answers

189k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...

Jonas

32.4k

asked Jun 12, 2011 at 13:36

133 votes

7 answers

276k views

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} ...

Majid Azimi

2,161

asked Mar 10, 2012 at 8:48

132 votes

2 answers

271k views

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 ...

Jonas

32.4k

asked May 18, 2011 at 8:27

All Questions

Related Tags