Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book [The Definitive guide to SQLlite](https://link.springer.com/book/10.1007/978-1-4302-3226-1) , where the author explains internal record format (Figure 9-5, page 351): Given table:

sqlite> SELECT * FROM episodes ORDER BY id LIMIT 1;
id   season  name
---  ------  --------------------
0    1       Good News Bad News

Its internal record format is:

| 04 | 01 | 01 | 49 |   | 00 | 01 | Good News Bad News |

> "The header is 4 bytes long. The header size reflects this and itself is encoded as a single byte. The first type, corresponding to the id field, is a 1-byte signed integer. The second type, corresponding to the season field, is as well. The name type entry is an odd number, meaning it is a text value. Its size is therefore given by (49-13)/2=18 bytes." Specifically I'm curious about TEXT attribute, in the example above we have a string of length 18 characters. And the rule for TEXT in SQLite is as follows:

Type Value     Meaning   Length of Data
----------     -------   --------------------
N>13 and odd   TEXT      (N-13)/2

What hapenns though when the string is longer ? It'll get out of range of that one byte.

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ events_table_in and events_table_out have exactly the same schema. Simply explained, I loop through the records of events_table_in, manipulate the records and want to append (insert) the manipulated records into events_table_out in the following fashion: OPEN recCurs FOR execute format('SELECT * FROM %s order by session_id, event_time', event_table_in); LOOP FETCH recCurs into rec; if not found then exit; end if; -- 1. do something with rec -- 2. insert the rec into events_table_out end loop; How can I save the rec into events_table_out?

Example: https://dbfiddle.uk/bCSwVpd9 Database:

CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TYPE entity_id AS (
  id bigint
);

Function:

CREATE FUNCTION get_entities (
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
  id bigint
)
LANGUAGE SQL
AS $BODY$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      -- filter by id list if provided
      entity_ids IS NULL OR id IN (
        SELECT
          id
        FROM
          entity_ids
      )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$BODY$;

The crutch is I want to write a paginated multi-select function which could work both from pagination info and a set of ids. The problem with the function above it crashes with:

ERROR:  relation "entity_ids" does not exist
LINE 22:           entity_ids

There are similar responses to this problem: [first](https://dba.stackexchange.com/a/263986/259283) , [second](https://stackoverflow.com/a/56753558/14481500) . However they revolve around argument being an identifier string, not a composite record type and also use plpgsql, which might or might nor be important.

There was a rather [innocuous question about adding dates and times in SQL Server](https://dba.stackexchange.com/questions/31669/how-do-i-add-minutes-to-a-time-data-type#question) 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**

From PostgreSQL documentation , it is possible to cast an anonymous record type to a named composite type at a function call site. Quoting the example there: CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- No cast needed since only one getf1() exists SELECT getf1(ROW(1,2.5,'this is a test')); However, the implicit cast of record type does not seem to work for an array of anonymous records. If I slightly change the above example to work on an an array function taking getf1a(mytable[]) as below, I get an error: CREATE FUNCTION getf1a(mytable[]) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; SELECT getf1a(ARRAY[ROW(1,2.5,'this is a test')]); The error says: => ERROR: function getf1a(record[]) does not exist LINE 1: SELECT getf1a(ARRAY[ROW(1,2.5,'this is a test')]); The anonymous and named array record types are compatible and there is no ambiguity. It would be nice if the cast is implicit. My question is: Is there a way to make the implicit cast work for array record types too? (This is with PostgreSQL 12.x)

Let's say we have one SQL Server instance on one side: - SQL Server 2008 R2 SP3 - Oracle 11.2 client installed with OLEDB Drivers for ODBC .. and an Oracle database instance on another side: - Oracle Database 11.1.0.7 PSU 24 - InstanceName: MERCURE I created two database links on the SQL Server instance: 1. MERCURE (using ODBC Server DSN and MSDASQL Provider) 2. MERCURE_OLE (using OraOLEDB.Oracle) On the Oracle instance (with instance name MERCURE), I created a schema and table as follows: CREATE USER JEL_PERF_TST identified by "XXX"; GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE to JEL_PERF_TST; ALTER SESSION SET CURRENT_SCHEMA=JEL_PERF_TST; -- select DEFAULT_TABLESPACE from dba_users where username='JEL_PERF_TST'; -- USERS -- create a table for testing (Taken from https://use-the-index-luke.com/sql/example-schema/oracle/3-minute-test) CREATE TABLE TestTbl ( id NUMERIC NOT NULL, date_column DATE, a NUMERIC, b NUMERIC, text VARCHAR(255), state CHAR(1), PRIMARY KEY (id) ); INSERT INTO TestTbl SELECT level , SYSDATE - level , MOD(level,1234) , TRUNC(DBMS_RANDOM.VALUE(1, 10)) , DBMS_RANDOM.STRING('l', 20) , DECODE(MOD(level, 5), 'X', 'A') FROM dual CONNECT BY level <= 50000; So, basically, I have 50.000 rows in my table. Now, let's run the following queries on SQL Server instance: First, let's activate statistics: set statistics io on; set statistics time on; Then, run a first query using OraOLEDB.Oracle provider: SELECT * FROM [MERCURE_OLE]..[JEL_PERF_TST].[TESTTBL]; Here is what I got: SQL Server parse and compile time: CPU time = 32 ms, elapsed time = 274 ms. (50000 row(s) affected) SQL Server Execution Times: CPU time = 2199 ms, elapsed time = 5769 ms. Performances aren't pretty good (more tha 8.5 ms for a single row) but it doesn't matter here. We have 100% of rows returned. Let's now run the query using OPENQUERY and same Linked Server: SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 103 ms. (50000 row(s) affected) SQL Server Execution Times: CPU time = 2542 ms, elapsed time = 6716 ms. Same here, performances are bad but we have 100% of rows returned. Now, let's run the exact same query, but using MSDASQL provider: select * from openquery(MERCURE,'select * from JEL_PERF_TST.TestTbl'); It results in: SQL Server parse and compile time: CPU time = 15 ms, elapsed time = 107 ms. (**49901** row(s) affected) SQL Server Execution Times: CPU time = 1357 ms, elapsed time = 3932 ms. Timing is better, but where are my 99 missing rows ?? I've tried many changes to my ODBC connection descriptor (Enable/disable result sets, raise the Fetch Buffer Size, Use MSDTC or not, etc.). Nothing worked. Any clue ?

I ran across this questions trying to dive into a computer measurements worksheet online and for the life of me could not figure it out. This is the question: Assume each record in a database requires 1Kib and there should be approximately 5 million records that need to be stored. Estimate the amount of storage needed in gibibytes for the database. Any help would be extremely appreciated because I'm completely lost on this question even though it sounds like it should be simple. Thanks in advance.

I have a function that declares: DECLARE r RECORD; The data on this record varies and at some point I need to know the column names. How do I get the column names of a RECORD in another record ?

I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection: - one vector containing data whose type is unknown at call time (an array) - one string value containing information as how to interpret this data Here is an illustration of the function architecture: What should I use as a return type / return value / return instruction so both these informations are returned? CREATE OR REPLACE FUNCTION public.retrieve_vector( vname bigint) -- the vector name: actually a column name RETURNS RECORD -- * * * is that the return type I need? * * * LANGUAGE 'plpgsql' VOLATILE AS $BODY$ DECLARE info text; -- data interpretation information BEGIN -- Retrieve data information from, say, the such-information table SELECT info FROM public."InfoTable" WHERE name = vname INTO info; -- (this is actually a more complicated process) -- Retrieve the vector and return both RETURN info, (SELECT vname FROM public."Vectors"); -- (this is also more complicated) -- * * * but RETURN a, b; is not possible like this, right? How to do? * * * END $BODY$; As dummy instances, I expect this function to return something like retrieve_vector('temperatures') -> ['old thermometer', [0, 1, 5, -3]] or retrieve_vector('words') -> ['small words only', ['at', 'the', 'and', 'or']]. (To those who might ask: in reality this is about meta-programming, so the "info" is actually refined *type* information.) In such a situation, I think that: - it is legitimate that this function returns two values - it is legitimate that the vector type is unknown at call time - it is legitimate that the first value is scalar (single value) and the other is a vector (a column) - it is legitimate wanting to connect the database only once Is it possible defining a function with such a flexible signature? How would I do this?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? [Currently, I'm doing it quite explicitly,](https://github.com/houstondatavis/data-jam-february-2018/blob/master/sql/06_funcs.sql) SELECT (a,a,a,a,a,a)::census.series_id FROM census.alldata CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a); And, I'm wondering if there is a nicer way to do this than that. Ideally, I would just be able to cast text[] into any record type, and the coercion would take place under the hood. When I try this I get an error though, SELECT a::census.series_id FROM census.alldata CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a); ERROR: cannot cast type text[] to census.series_id LINE 1: SELECT a::census.series_id

Hi I am a civil engineer with some experience of programming but I am not familiar with the vast range of options available today. Hope you could give me any pointers the best way to proceed. I want to make and query a database of ground level survey measurements in a grid format. There will be many measurements for each grid location at various times through the earthmoving job so there is a 4th dimension of time. The observations will most likely be read in from a text file. In each record there will be an (2 x integer) grid position (row and column) a (floating point) ground level and various string information codes (maybe up to 30 characters in total). The grids could be about 10000 rows x 10000 columns. Not every location on the grid would be have a record in each survey, but they would typically have up to a hundred records. A lot of the grid locations would have no records at all (the site will not be perfectly rectangular). I want to search the records, extract data and do calculations eg calculate the lowest or highest ground level for each grid location. I am fairly confident I would have the ability to program this fairly simply in a language like FORTRAN, BASIC or C using arrays. A lot of the array elements would be empty though and I am guessing that this is not the right way to do it though and big databases like this need special tools that I will have to learn how to use. I am thinking of possible options for the platform - 1. Use a database program. I am not familiar with how powerful these can be but I imagine they would have a lot of overhead with GUI. 2. Use SQL? This I don't know much about but it seems to be the language for databases. I have always used imperative languages rather than declarative and as I understand from wikipedia that SQL is declarative I am a bit nervous of the change. I don't fully understand the process for using it. Is there a compiler that makes console programs? Is the database stored on disk? Sorry for such stupid questions. 3. Use an API like c-treeACE? I think this may be the way to go offering me the familiarity of a "do this, then do that" language (unfortunately this is the way I think as an engineer!). But I am hoping that the behind the scenes memory and processing management offered by the API would be superior to what I could achieve with huge arrays. 4. Or could i do it with an object orientated language and let the computer worry about the storage requirements. eg if i stored the records as objects with methods and properties that would help me get the results I need out of each record - would it be a huge bloated program compared to 3) There are likely to be hundreds of millions of records and I want to be able to query and process them in minutes not hours (preferably seconds!) on a modern PC running windows. To be more specific mine is an i7 processor with 6Gb ram and 120Gb SSD running Windows 7 64 bit. Hope someone has time to share a couple of words of wisdom with a newbie.

i encountered a very strange behaviour on mysql database. I have this complicated query: SELECT category, subcategory, sum(hours), count(hours), group_concat(concat(user,': ', comment) separator ' ') as comments FROM duomenys, workers WHERE duomenys.user=workers.full_name AND date BETWEEN '2017-05-23' AND '2017-05-25' AND user='USERNAME' AND departament='Some departament' GROUP BY category, subcategory ORDER BY id ASC; In short it selects records from time interval (concats identical comments and sums hours). For example: Category Subcategory Hours Count Comments Category1 Subcategory1 2:30 3 Username: User comment. The problem is, if i increase time interval BETWEEN '2017-05-22' AND '2017-05-25', one record from 2017-05-24 is not displayed. Not sure if it's only this one, a user spotted it missing. I can select any date, or even interval of one day date, it shows ok. But if i select 22 or earlier as starting date, it dissapears from results. The record is normal, same as others, the query is the same, i cannot spot anything unusual. Same behaviour happens in phpmyadmin if i use this query directly on the database. Table structures are very simple, no external keys, etc.: CREATE TABLE IF NOT EXISTS duomenys ( ID int(11) NOT NULL AUTO_INCREMENT, Ivedimas datetime NOT NULL, Data date NOT NULL, Category varchar(255) NOT NULL, Subcategory varchar(255) NOT NULL, Hours int(11) NOT NULL, Comment text NOT NULL, User varchar(64) NOT NULL, PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18871 ; CREATE TABLE IF NOT EXISTS workers ( name varchar(20) NOT NULL, full_name varchar(80) NOT NULL, skyrius varchar(80) NOT NULL, departament varchar(80) NOT NULL, PRIMARY KEY (vardas) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; I'm sorry if names don't match somewhere, i tried to translate fields to english, for easier reading. EDIT: I thought i found the answer. After carefully examing that record, i found this quotation mark used in the record " instead of “„ like on every other records. But changing the quotation mark didn't help. And it shouldn't, because i use mysql_real_escape_string before inserting into databse. That record looks like this: SCR-4.1.1-2017 „Scrambled ciklo, scrambled ir daliųjų medžiagų vežimo sertifikatų išdavimo taisyklės“ projekto peržiūra (pakartotinai).

Using the UUID data type with its 256 chars seems like overkill, while assigning an auto-increment int4 seems less robust. Is there a more robust way to assign a record id without using uuid or an auto-increment int4?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; _oldid integer; _currentid integer; BEGIN OPEN _roles FOR SELECT role FROM roles WHERE id IN (SELECT idrole FROM user_roles WHERE iduser=4); EXECUTE format('SELECT oldid FROM users WHERE id = 4') INTO _oldid; EXECUTE format('SELECT id FROM oldtable WHERE id = $1') USING _oldid; FOR _record IN _roles LOOP RAISE NOTICE 'The role is = %', _record; EXECUTE FORMAT ('UPDATE oldtable SET ''%s'' = 1 WHERE id = $1', _record) USING _oldid; END LOOP; END; $$ LANGUAGE plpgsql; So, this return a sintaxe erro because the correspondent value for _record contain parentheses and apostrophe (e.g.: *UPDATE oldtable SET '(rolename)' = 1 WHERE id = 2*). Of course, these type is record and not varchar... Well, how to use the value from record like as varchar? ********* For more information if needed TABLE USER id | user -------------- 1 | 'user1' -------------- 2 | 'user2' -------------- TABLE ROLES id | role ---------------- 1 | 'role' ---------------- 2 | 'role2' ---------------- 3 | 'role3' ---------------- 4 | 'rolex' ---------------- TABLE USER_ROLES iduser | idrole ---------------- 1 | 1 ---------------- 1 | 2 ---------------- 1 | 3 ---------------- 1 | 4 ---------------- 2 | 3 ---------------- 2 | 2 ---------------- TABLE WRONG_OLD_TABLE **:)** iduser | role1 | role2 | role3 | role_x | role_y ... ----------------------------------------------------- 1 |1 |1 |1 |1 |1 | ----------------------------------------------------- 2 |0 |1 |1 |0 |0 |

I am a new to Pl/sql and I need to learn to write simple pl/sql scripts. Is there any difference between cursors and records in PL/sql? So far I find them both similar.

Hi I would like to ask if there is a smart way to change the record position of a table in mysql. My problem is this: I have a table with statistics data and when I recover all the details in a table called "details" I get the following result: id | name 1 | Residents 2 | In age from 0 to 6 3 | In age from 6 to 14 4 | In age from 14 to 30 5 | In age from 30 to 66 6 | Total school population 7 | Students in kindergarten 8 | Students in high school 9 | ... 10 | ... 11 | 66 years and over I would like the 11th record to be right after the 5th, thus right after "In age from 30 to 66". Is there a smart way to do that? Thanks!

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table VALUES ' || NEW; EXECUTE 'INSERT INTO my_table VALUES ' || NEW.*; EXECUTE 'INSERT INTO my_table SELECT * FROM ' || NEW;

What's the best way to create a record keeping table for all actions in my database? I have a number of tables inside one database and I want to create another that records all movements in every other table - adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button. Is there a tried and tested method for this **or** a native feature of (InnoDB) MySQL useful for this? If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?