Questions tagged [row]
65 questions
62
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,365
- 3
- 43
- 88
45
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,321
- 4
- 15
- 26
36
votes
2 answers
Postgres multiple columns to json
I am running postgresql 9.3.4. I have a table with 3 fields:
id name addr
--- ---- ----
1 n1 ad1
2 n2 ad2
...
I need to move the data to a new table with fields like:
id data
--- ----
1 {'name': 'n1', 'addr': 'ad1'}
2 …
AliBZ
- 1,509
- 5
- 15
- 26
25
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
- 437
- 1
- 5
- 11
12
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
- 233
- 1
- 2
- 7
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
- 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…
NO WAR WITH RUSSIA
- 54,954
- 34
- 200
- 411
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
- 153
- 2
- 2
- 6
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…
NO WAR WITH RUSSIA
- 54,954
- 34
- 200
- 411
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
- 500
- 5
- 12
2
votes
1 answer
Does a SQL server unique constraint partially or fully fail a multi-row write?
If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
user32234
2
votes
1 answer
Why doesn't row data show up in SQL Developer?
I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows:
BEGIN
FOR i in 1..10 LOOP
INSERT INTO TABLE1 (COLUMN1)
VALUES (i);
END…
Brett Porter
- 23
- 1
- 3
2
votes
1 answer
Why is the ROW keyword needed when constructing a row with one element, but not two?
In PostgreSQL 11, the following expression
UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c'
is accepted and performs the update.
But for a single column / value, like
UPDATE test_table SET…
Amaterasu
- 21
- 2