Most Popular
1500 questions
56
votes
8 answers
Get multiple columns from a select subquery
SELECT
*,
p.name AS name,
p.image,
p.price,
(
SELECT ps.price
FROM product_special ps
WHERE p.id = ps.id
AND ps.date < NOW()
ORDER BY ps.priority ASC, LIMIT 1
) AS special_price,
(
…
Sparctus
- 663
- 2
- 7
- 8
56
votes
4 answers
Is it possible to store and query JSON in SQLite?
I need to store JSON objects in a SQLite database,
and then do complex queries on it.
I did a table like this:
+--------------------------------------+
|document | property | string | number|
+--------------------------------------+
|foo | …
tuxlu
- 561
- 1
- 4
- 3
55
votes
7 answers
How to get all roles that a user is a member of (including inherited roles)?
Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest".
create role authors;
create role editors;
create user maxwell;
create user ernest;
grant authors to editors; --editors can do what…
Neil McGuigan
- 8,653
- 5
- 42
- 57
55
votes
5 answers
How to efficiently copy millions of rows from one table to another in Postgresql?
I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history one.
What I did was to run:
INSERT INTO history…
Milovan Zogovic
- 1,463
- 3
- 15
- 20
55
votes
6 answers
Troubleshooting high CPU usage from postgres and postmaster services?
I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?
asadz
- 655
- 1
- 5
- 6
55
votes
5 answers
How can I use a default value in a Select query in PostgreSQL?
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?
E.g. something like this:
SELECT MAX(post_id) AS max_id DEFAULT…
Jonas
- 33,945
- 27
- 62
- 64
55
votes
6 answers
How can I get the actual data size per row in a SQL Server table?
I found this script that seems to return the row size per defined data type lengths.
I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 bytes (whatever MS recommends).
Anthony
- 653
- 1
- 5
- 5
55
votes
5 answers
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: Unknown collation utf8mb4_0900_ai_ci
Then I went…
Frosty
- 551
- 1
- 4
- 3
55
votes
6 answers
How to import a .sql file in MySQL?
I am trying to import a .sql file using MySQL Workbench and I get this error:
ERROR 1046 (3D000) at line 28: No database selected
I have first created an empty database called with the same name as the .sql file but it doesn't work.
I have also…
Barbara Dreamer
55
votes
7 answers
Why does ORDER BY not belong in a View?
I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)
I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the SELECT statement querying the view.
But being…
ngmiceli
- 653
- 1
- 5
- 6
55
votes
1 answer
postgresql: how do I dump and restore roles for a cluster?
Where are roles stored in a cluster, and how do I dump them?
I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:
psql:mydump.sql:3621: ERROR: role "myrole" does not exist
So apparently the dump of…
Rob Bednark
- 2,253
- 6
- 22
- 22
55
votes
10 answers
restore table from .frm and .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I…
Tong Wang
- 653
- 1
- 6
- 5
55
votes
3 answers
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default).
I understand that InnoDB will map the…
Googlebot
- 4,551
- 26
- 70
- 96
55
votes
7 answers
If a person's name is Null then how would it break the database?
I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc.
I am not well versed in databases and I do not use it…
Souradeep Nanda
- 685
- 1
- 5
- 8
55
votes
4 answers
Check if a user exists in a SQL Server database
I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database.
This is what I have tested:
USE [MyDatabase]
GO
IF NOT EXISTS (SELECT name
FROM [sys].[server_principals]
WHERE name…
VansFannel
- 1,873
- 5
- 23
- 36