Questions tagged [row]
71 questions
68
votes
8 answers
What is the difference between a "record" and a "row" in SQL Server?
There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.
So how do we differentiate between these related terms and how we use them properly?
Row
Record
swasheck
- 10,755
- 5
- 48
- 89
60
votes
7 answers
Select columns inside json_agg
I have a query like:
SELECT a.id, a.name, json_agg(b.*) as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
How can I select the columns in b so I don't have b.item_id in the JSON object?
I have read about ROW, but it returns a…
Yanick Rochon
- 1,651
- 4
- 20
- 28
51
votes
2 answers
Replace multiple columns with single JSON column
I am running PostgreSQL 9.3.4. I have a table with 3 columns:
id
name
addr
1
n1
ad1
2
n2
ad2
I need to move the data to a new table with a JSON column like:
id
data
1
{"name": "n1", "addr": "ad1"}
2
{"name": "n2", "addr":…
AliBZ
- 1,827
- 5
- 17
- 27
26
votes
4 answers
How to limit maximum number of rows in a table to just 1
I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as…
Dib
- 447
- 1
- 6
- 13
13
votes
5 answers
Calculate row value based on previous and actual row values
Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)
I want to calculate the balance following these rules:…
Mohamed Anis Dahmani
- 243
- 1
- 2
- 7
13
votes
3 answers
How to insert 10000 new rows?
Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:
INSERT INTO bins (id)
VALUES (1)
to create a single…
Andrew
- 131
- 1
- 1
- 3
7
votes
2 answers
Create smaller row from alias and preserve column names
Using Postgres:
SELECT users."name" AS "name"
, array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
ON users.id=user_sites.user
JOIN remodel.sites AS sites
ON…
Gauss
- 73
- 4
6
votes
1 answer
Increment value in row if it exists, otherwise create row with value?
Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I go about having a new _views row for instance…
Jerry Tunin
- 97
- 1
- 1
- 6
6
votes
1 answer
Using a type in place of a column definition list?
Running the below, I get "a column definition list is required for functions returning "record"".
SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR: a column definition list is required for functions returning "record"
LINE 1: SELECT…
Evan Carroll
- 65,432
- 50
- 254
- 507
5
votes
2 answers
Oracle ROWID, can we assume it is sequential in a insert-only scenario?
How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why?
I had a situation where I had to verify whether new records were added to table which has no sequence or date columns to use as reference. I…
Anthony
- 163
- 2
- 2
- 6
4
votes
5 answers
How to update table records in reverse order?
I've a table Student
Id Name Mark
1 Medi 10
2 Ibra 15
3 Simo 20
and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order:
Id Name Mark
1 Simo 20
2 Ibra 15
3…
TAHER El Mehdi
- 292
- 2
- 9
4
votes
3 answers
Is there anyway to UPDATE a series of columns on a row with a composite type record/row?
Take a table foo,
CREATE TABLE foo(a,b,c,d)
AS VALUES
(1,2,3,4);
I can insert into foo with a row type..
INSERT INTO foo
SELECT (foo).*
FROM foo;
INSERT INTO foo
SELECT (v).*
FROM ( VALUES (42,42,42,42) )
AS v;
But I can't update…
Evan Carroll
- 65,432
- 50
- 254
- 507
4
votes
2 answers
SQLite3 - remove duplicates by multiple columns
I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns.
Example:
id name age x
1 peter 25 II
2 peter 25 II
The table has tens of…
Milano
- 143
- 1
- 6
3
votes
2 answers
How to update ID=null values in table to incremental counter values?
On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows:
ID NaturalID Comment
1 franknfurther03071972 blahblah
2 …
Erik Hart
- 510
- 5
- 12
3
votes
1 answer
How to check for row lock level when FOR UPDATE is used in PostgreSQL?
Related to previous question here
From the pg_locks documentation, row level lock information is not available when FOR UPDATE is used in a SELECT statement.
Is there any way to check for row level lock when FOR UPDATE is used in a SELECT…
hunterex
- 147
- 4
- 10