Re: Order of update

Thanks for the pointer. I feel my doubts reflected. For such reasons, I prefer the UUID as surrogate key. No point in trying to establish an order or even id arithmetics. 21.04.2025 18:44:27 Adrian Klaver : > On 4/21/25 09:12, Thiemo Kellner wrote: >> I wonder if that is a co

Re: Order of update

I wonder if that is a corner case. Updating a unique key sounds to me like a design flaw in the first place.

Re: Order of update

Might that be a feature of or a flaw in the application design? I opt for the latter. Any application that needs updates, be it only in emergency cases, should take that into account.

Re: Order of update

Hm, deadlocks preventing order by. Never had that problem. Then again, I mostly have Oracle experience and no need for complicated updates. If I had, I'd rather think of chunking updates and orchestrate those before ordering within updates.

Order of update

Very interesting. But is the sort overhead worth it? Why not make the constraint deferrable before the update and switch back afterwards?

Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

El 25-03-25 a las 18:58, Christoph Berg escribió: Re: Tom Lane You might be best advised to create a composite type like "location (long bigint, lat bigint)" and use an array of that. Or use the geometry types from PostGIS, since you are probably going to do geo lookups on these points anyway

Creating files with testdata

DbVisualizer https://www.dbvis.com/docs/ug/exporting-a-grid/?_gl=1*1aoko6w*_up*MQ..*_ga*MTA0NjM4MTcxNi4xNzQxNjc2MDc0*_ga_9P463C7BC6*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjAuODQ3OTY1NDMx*_ga_PTZV59MQGC*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjAuMA..#generating-test-data Or DBeaver https://dbeaver.com

Re: Ideas about presenting data coming from sensors

13.02.2025 10:54:05 Achilleas Mantzios - cloud : > If we followed a strict normalized approach then we would create additionally > 11 tables each tag of type c) . And we are not guaranteed that the same tags > would have the same structure over the whole fleet/manufacturers. So we are > thinki

Re: Lookup tables

06.02.2025 22:04:34 Peter J. Holzer : >> I might see what you want to point out. E.g. the table is COLOURS. The >> rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. >> Now you load these values into the dropdown box that sports RED, BLUE, >> GREE and so on. While someone select

Re: Lookup tables

El 05-02-25 a las 22:19, Michał Kłeczek escribió: But you can guarantee that if you change the value of the key after the user displays it - the user will get an error on submission (whereas with the surrogate key it would happily proceed without user noticing). As you very rightly say happil

Re: Lookup tables

El 05-02-25 a las 13:55, Michał Kłeczek escribió: A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that. That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data. Even with cas

Re: Lookup tables

El 05-02-25 a las 19:13, Michał Kłeczek escribió: Only if you do not see the primary key as the main immutable value identifying an object, entity, you name it. Surrogate key cannot identify any (real) object by definition :) What object is identified by PK value 42 in “restaurants” table? Wh

Re: Lookup tables

El 04-02-25 a las 18:08, Michał Kłeczek escribió: Reality tends to become so ambiguous as to not be reflectable (two entirely different restaurants eventually, within the flow of time, carry the very same name). A primary key is very likely not the proper place to reflect arbitrary business logi

Re: Lookup tables

04.02.2025 18:12:02 David G. Johnston : > On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek wrote: > > Well, we were talking about lookup tables and not entity modelling... I am under the impression that a lookup table IS an entity. You find them in star and snowflake models alike. > >> >> Havi

Re: Lookup tables

04.02.2025 18:31:09 Michał Kłeczek : > >> On 4 Feb 2025, at 18:27, Thiemo Kellner wrote: >> >>  Unless the lookup table is actually a check constraint one can use to >> populate dropdown boxes in an interface. > > That is even worse because it ceases be

Re: Lookup tables

04.02.2025 18:31:09 Michał Kłeczek : >>  Unless the lookup table is actually a check constraint one can use to >> populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might > select something different than what they see on the screen.

Re: Lookup tables

Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface. Cheers Thiemo

Re: Plans for partitioning of inheriting tables

column constraints and table constraints. Not-null constraints are always copied to the new table./ 02.11.2024 00:53:53 Adrian Klaver : > On 11/1/24 16:10, thi...@gelassene-pferde.biz wrote: >> Adrian Klaver escribió: >> On 11/1/24 13:47, Thiemo Kellner wrote: >>>>

Re: Plans for partitioning of inheriting tables

It looks to me basically to be a "create table A as select * from B where false". 01.11.2024 20:38:15 Adrian Klaver : > On 11/1/24 12:16, thi...@gelassene-pferde.biz wrote: >> Thanks, I shall have a look into it. I was under the assumption the the >> create table like would create no more than

Plans for partitioning of inheriting tables

Am 25.10.2024 um 17:57 schrieb Adrian Klaver: I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from a base table. The documentation you provided the URL seems to speak of realising partitioning by using inheritance. This needs a code example

Re: Plans for partitioning of inheriting tables

Am 25.10.2024 um 17:57 schrieb Adrian Klaver: I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from a base table. The documentation you provided the URL seems to speak of realising partitioning by using inheritance. This needs a code example

Re: Plans for partitioning of inheriting tables

Thanks for taking this up. 24.10.2024 22:44:11 Adrian Klaver : > > 1) Have you looked at?: > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE I do not feel it applies to my case. I tried to create a partitioned table that inherits columns from

Re: Plans for partitioning of inheriting tables

24.10.2024 22:58:39 David G. Johnston : > > My impression of things is that directly using “inherit” for table creation > is considered deprecated at this point.  No one has interest in expanding on > the feature nor even recommends it be used in new development.  That > particular unique featu

Manual query vs trigger during data load

Hi To me it seems possible to create an insert select, e.g. Insert into tab1 (val1, val2) Select valA, valB >From tab2 Where valC = :param1 Cheers Thiemo

Re: Better way to process records in bash?

You maybe even could use plpythonu.

Better way to process records in bash?

Hi What is this "something" that it cannot be calculated within the dB?

Re: ssh to DB server and su normal users very slow :

Hi What does the server side do in the wait time? Cheers Thiemo

Re: Getting specific partition from the partition name

Thanks. Nice one. Would not have thought to try.

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Am 09.04.2024 um 17:18 schrieb Adrian Klaver: Because you did not do?: PG_CATALOG.PG_ROLES.ROLNAME%type Thanks

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Am 09.04.2024 um 17:18 schrieb Adrian Klaver: Because you did not do?: PG_CATALOG.PG_ROLES.ROLNAME%type Oh, right. Sorry. What an oversight.

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Thanks for taking this up. Am 09.04.2024 um 17:09 schrieb Adrian Klaver: On 4/9/24 07:59, Thiemo Kellner wrote: [Code: 0, SQL State: 0A000] ERROR: References to other databases are not implemented: pg_catalog.pg_roles.rolname    Position: 298 [Script position: 334 - 361

[Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Hi I have the following function code. When trying to install, it gives me [Code: 0, SQL State: 0A000] FEHLER: Verweise auf andere Datenbanken sind nicht implementiert: pg_catalog.pg_roles.rolname Position: 298 [Script position: 334 - 361] To the best of my knowledge, pg_catalog is a sche

Re: Is this a buggy behavior?

Am 25.03.2024 um 07:59 schrieb Laurenz Albe: On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: How can that be forgotten? This information ends up in the data catalogue eventually! It *is* stored in the catalog. But if you add a primary key, that is tantamount to saying ALTER

Re: Empty materialized view

My bad as always. Having the refresh after the filling of the tables does the trick. Thanks for your help.

Re: Empty materialized view

Am 24.03.2024 um 22:06 schrieb Adrian Klaver: The view session is on auto commit. (It's sole purpose to query stuff and not to have explicitly terminate transactions do to syntax errors and so on.) Autocommit will only affect actions in that session, it will not make the other sessions action

Re: Empty materialized view

Am 24.03.2024 um 21:50 schrieb Adrian Klaver: On 3/24/24 13:36, Thiemo Kellner wrote: It does depending on the order of viewing. Namely if you viewed the 'old' empty MV in the outside session before you dropped/created the 'new' MV and committed the changes. Someth

Re: Empty materialized view

Am 24.03.2024 um 21:30 schrieb Adrian Klaver: On 3/24/24 13:11, Thiemo Kellner wrote: Confirmed in the same session that created it or in a different session? Different session, not knowing what that mattered. Excerpt of the installation protocol: … ## tenth level ## Set materialised view

Re: Empty materialized view

Am 24.03.2024 um 20:56 schrieb Erik Wienhold: Maybe you executed REFRESH in a transaction but did not commit it? While I can see the point for the refresh (but there actually is a commit), I cannot hold it valid for a create with data when the mv actually is created (confirmed by being empt

Empty materialized view

Hi I have created a materialized view with "with data". And I refreshed it with "with data". The query of the mv returns records when executed outside the mv. I would appreciate help with respect to what I miss that my mv is empty. You might want to have a look at the code attached. Kind reg

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:43 schrieb Christophe Pettus: The situation is much more like the customer saying, "I understand that the standard paint for this car is red, but I wish it painted blue instead." Not in the least. Declaring the column to be NULL is explicitly requesting the car be blue. An

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:15 schrieb Christophe Pettus: I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects (not even really a side-effect) of that request is that the column i

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer: postgres=# create table bla(i int null primary key); CREATE TABLE postgres=# \d bla     Table "public.bla"  Column |  Type   | Collation | Nullable | Default +-+---+--+-  i  | integer

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:35 schrieb sud: On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > wrote: Do you specifically mean that 'null'  keyword is just not making any sense here in postgres. But even if that is the case , i tried inserting nothing (hoping "nothing" is "null" in

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:39 schrieb Erik Wienhold: And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true, I do not see why the information that a not nul

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like a bit too much work. If I understood c

Re: Is this a buggy behavior?

Am 24.03.2024 um 15:54 schrieb Erik Wienhold: This is required by the SQL standard: columns of a primary key must be NOT NULL. Postgres automatically adds the missing NOT NULL constraints when defining a primary key. You can verify that with \d test1 in psql. To me, this behaviour, while cor

Re: PostgreSQL as advanced job queuing system

Am 22.03.2024 um 14:15 schrieb Fred Habash: We developed a home-grown queue system using Postgres, but its performance was largely hindered by que tables bloating and the need to continuously vacuum them. It did not scale whatsoever. With some workarounds, we ended up designing three sets of

Re: select results on pg_class incomplete

https://wiki.postgresql.org/wiki/Monitoring Thanks for the URL. I am not too keen to re-invent the wheel. Although it teaches me on PostgreSQL.

Re: select results on pg_class incomplete

You could also create a PostgreSQL foreign server for each of the other databases, which would let you issue a query to UNION together the results of a query on all of the catalogs. This would require creating a foreign table for pg_class in the other databases. Thanks. So many possibilit

Re: select results on pg_class incomplete

You solve a problem that no one has. Data belonging together may still be divided into schemas in a database. Thus, the metadata is also reported and archived individually per database. I am not sure, we are taking about the same problem, but would be surprised to be the only one having exper

Re: select results on pg_class incomplete

Am 14.03.2024 um 21:03 schrieb David Rowley: Yeah, maybe dblink and a LATERAL join might be an easy way. Something like: create extension dblink; select d.datname,c.relname from pg_database d, lateral (select * from dblink('dbname='||d.datname,$$select relname from pg_class where relname = 'p

Re: select results on pg_class incomplete

Thanks for the enlightenment. A pity. I suppose, there is no working around this? Am 14.03.2024 um 18:01 schrieb Adrian Klaver: On 3/14/24 09:41, Thiemo Kellner wrote: Hi I am trying to access PostgreSQL meta data, possibly in a vane attempt to get size data. I use DbVis with a connection

select results on pg_class incomplete

Hi I am trying to access PostgreSQL meta data, possibly in a vane attempt to get size data. I use DbVis with a connection as shown in https://ibb.co/2SDzhXt . I try to get information on a regular table "umsaetze". When doing the DbVis object I can see them - https://ibb.co/WxMnY2c . If I ex

Re: Me again with an insert trigger problem

Am 27.02.2024 um 23:20 schrieb Adrian Klaver: On 2/27/24 14:11, Thiemo Kellner wrote: It is a habit of mine to pad conditions in the where clause. This way, it is easy to comment/uncomment parts of the clause for testing purposes. Coming from Oracle, I missed that using "true&quo

Re: Me again with an insert trigger problem

Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME   from NODE⠒V where 1 = 1    and ID = new.NODE⠒ID    and 1 = 1; is supposed to be doing especially the 1 = 1 tests? The selec

Re: Me again with an insert trigger problem

Thanks. 27.02.2024 19:09:50 Adrian Klaver : > > On 2/27/24 9:49 AM, Thiemo Kellner wrote: >> Hi >> >> I am surprised that my before insert trigger function does not insert any >> rows into NODE_GOOD. >> >> I was under the impression that the trigger

Me again with an insert trigger problem

Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "Drop-

Re: pPL/pgSQL restriction on characters for copying types?

Shame on me. My bad. It was the order of installation that did not work. Sorry for that. I was mislead by the error message. If an object is missing I would not expect an invalid type name message. Thanks Am 26.02.2024 um 17:53 schrieb Thiemo Kellner: Thanks for the hint and care. The install

Re: pPL/pgSQL restriction on characters for copying types?

Thanks for the hint and care. The install script has a set statement already and I now added the search_path clause to no avail. Please find the entire code attached and a screenshot from the error. Am 26.02.2024 um 17:35 schrieb Tom Lane: Thiemo Kellner writes: However, I want to create a

Re: pPL/pgSQL restriction on characters for copying types?

Oh, I totally forgot to mention that I ran the scripts with DbVisualizer against a 16.1 (Debian 16.1-1.pgdg110+1) server using PostgreSQL JDBC Driver 42.6.0 . Am 26.02.2024 um 16:51 schrieb Thiemo Kellner: Hi My names can contain a special character (⠒), e.g. to separate the donator object

pPL/pgSQL restriction on characters for copying types?

Hi My names can contain a special character (⠒), e.g. to separate the donator object from the name or the name from the type. Typically, I use this with foreign keys, e.g. table PARENT has the surrogate key ID, the table CHILD would then have the column PARENT⠒ID. That way, I can use the unde

Re: client/server versions

Am 21.11.2023 um 13:18 schrieb Dick Visser: It is possible, it's just that there is no real need to at the moment, ao we have not spent that much effort on the topic. At any time there will always be newer upstream versions. Ok, fair enough, but it puzzles me that the effort shall be put int

Re: client/server versions

Hi Dick Out of curiosity, what is the reason there is this zoo of versions. Is it impossible to align them to one version? Cheers Thiemo

Re: Conditional compilation

checks for every and each logging statement put (as the check has been done on installation already). begin     do_something;     $if check_if_env_is_dev $then         do_some_logging;     $end     do_more_stuff; end; Am 12.11.2023 um 16:58 schrieb Tom Lane: Ron writes: On 11/12/23 09:

Conditional compilation

Hi Does PostgreSQL have something like Oracle's conditional compilation? This is sort of an if then statement that gets evaluated on compilation/installation time of PL/SQL code. If the condition is met, the code until the $END gets compiled. It is even possible to switch on/off parts of sing

Re: Detection of which attributes should get set in update trigger

Thanks for the reply. I confirm the behaviour. Your explanation makes sense if I consider having read that with an update the is a complete new record version written. Am 10.11.2023 um 14:35 schrieb David G. Johnston: On Friday, November 10, 2023, Thiemo Kellner wrote: Hi all I

Detection of which attributes should get set in update trigger

Hi all I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone. Is there a

Rule system (and triggers)

Hi all I am afraid, I have not understood the rule system yet. I have got 4 tables and a view over all of them. ladevorgaenge  kanton  tarifgruppe 0..1   \       |  /        \  | /         \ |    / |    |    |     /|\ /|\ /|\   tarif_progress

Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

Hi Why do you need checksums? Can you not employ a full outer join? My though behind this is that checksumming is quite costly cpuwise and you have to fiddle with each and every relevent column, be it as part of the checksum string be it as part of the joiner. The joiner would have the advant

Purely declarative FKs

Hi Please bear with me, if this is the wrong place or this is an old question answered over and over again. Context: In my professional life I rarely come across projects/applications where there are foreign keys on the database. This is due to loading freedom not due to that there actually

Re: an difficult SQL

Hi Rafal You first could select the three users with the most recent entries with a windowing function (https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) putting it into a with query (https://www.postgresql.org/docs/15/sql-select.html), in following with querie

Re: Window function?

Hi Robert Interesting problem. I need to think about it. You need to figure out when Input changes. You can achieve this by using lead or lag (depending of the sort direction over start) https://www.postgresql.org/docs/current/functions-window.html . Hope this nudges you to a solution. Kind

Re: How to check if a materialised view is being updated?

I’ve got a materialized view as a source for my ETL-process, and the materialized view takes several hours to refresh. During which it is locked for queries. Would it be an option to split the process into a cascade of materialized views to minimize the actual time of lock? So I’m lookin

Re: Do we need a way to moderate mailing lists?

Also, could it be possible to make messages plain text? I see a lot of varying fancy fonts and I hate that. I even hate it more when people post messages not properly trimmed or messages that need formatting preserved such as select output, i.e. table data, explain plans, etc. Proportiona

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Quoting Michael Lewis : Still, no feedback on the effect that a truncate call is having on the DB and may be doing more than intended fairly easily. I am not in the hackers group so I couldn't say this feature would not be implemented. It just seems unlikely given the philosophies of that

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : On 1/3/21 1:44 PM, Thiemo Kellner wrote: So is the below still only going to fire on INSERT? If so it will not deal with functions that disappear after the INSERT, which in the end makes it similar to my suggestion:) The point being you are taking a snapshot in

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Familiar with it, I have worked in farming(outdoor and indoor(greenhouse)) industries. Cool (https://en.wikipedia.org/wiki/Growing_degree-day). It is a measure for energy an organism can consume in a specific day for its development. Also used to anticipate pe

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Can you provide an outline form of what you are trying to accomplish? Hm, making myself understood. ;-) So from the very beginning. There is the concept of growing degree days (https://en.wikipedia.org/wiki/Growing_degree-day). It is a measure for energy an organis

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Well, I guess, I can put the correct schema at installation, but would have liked to have a more general approach. Furthermore, I think this also implies that installation can only be done by psql. :-s Why not grab the CURRENT_SCHEMA in the function?: DECLARE

Re: Possible trigger bug? function call argument literalised

Quoting Tom Lane : Thiemo Kellner writes: create trigger CALCULATION_METHOD_BR_IU before insert on CALCULATION_METHOD for each row execute function METHOD_CHECK(current_schema); Executing such, the string "current_schema" gets literalised, i.e. single quoted: Yu

Re: Possible trigger bug? function call argument literalised

Quoting Rob Sargent : The function definition doesn’t name any parameters? Nope, trigger functions cannot, according to documentation. Parameters can be passed as list/array of values though. I have no clue about why this needs to be so awful/awesome. -- S/MIME Public Key: https:

Re: Possible trigger bug? function call argument literalised

end if; insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*); return NULL; end; $body$; Running this version, I get another proof that the term was literalised: psql:common_calculation_method_insert.pg_sql:59: ERROR: Schema "current_schema" could not be foun

Possible trigger bug? function call argument literalised

Hi I created a table with trigger and according trigger and trigger function as drop table if exists CALCULATION_METHOD cascade; create table CALCULATION_METHOD (ID uuid not null default uuid

Re: SQL pretty pritner?

https://www.sqlinform.com/ Quoting stan : I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page brea

pgutils, pglogger and pgutilsL out

Hi all I do not mean to spam so please tell me if this is not the right place for release announcements of OSS software for PostgreSQL. Be it as may, I am happy to have: - pgutils out: providing very basic functionality for PostgreSQL base applications (https://sourceforge.net/p/pgutils/

Re: Use of ?get diagnostics'?

Hello Adrian Quoting Adrian Klaver : To get above I believe you will need to use GET CURRENT DIAGNOSTICS PG_CONTEXT: I actually use "get stacked diagnostics" to retrieve the exception place. And it works. I am not sure why I did no see it. However, I noticed, that the stack does not incl

Re: Use of ?get diagnostics'?

Hi Andrew Paste sites are for IRC, on the mailing list you should always attach the necessary details to your message. Ok, I was under the impression that paste site were preferable to attachments which generates traffic not everyone is interested in. Thiemo> the following exception wa

Use of ?get diagnostics'?

Hi all I try to create a function (code at https://pastebin.com/mTs18B90) using 'get diagnostics' to retrieve the number of affected rows. However, it throws the following exception was thrown: SQLSTATE: 42703 column "row_count" does not exist when I test it with drop table if exists TE

Re: Posible off topic ? pgmodeler

Quoting stan : What I am trying to do, at the moment is get a complete understanding of their bossiness model, regarding the source code for this project. Thanks for any input on this. To the best of my knowledge Raphael tries to fund its time on the development of pgmodeler with selling

Re: pgmodeler ?

Quoting Olivier Gautherot : This is the specific error message: Could not execute the SQL command. Message returned: ERROR: column pr.proisagg does not exist LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag... ^ HINT: Perhaps you meant to reference the column -- "They

Re: timestamp out of range while casting return value to function's return type

Karsten Hilbert : On Sat, Aug 24, 2019 at 12:57:07AM +, Thiemo Kellner wrote: Call: select utils.get_max_timestamptz(); -- Function -- create or replace function GET_MAX_TIMESTAMPTZ() returns timestamptz language plpgsql immutable -- Include the hosting schema into search_path so

Re: timestamp out of range while casting return value to function's return type

Hi Tom Thanks for replying so fast. You are absolutely right. I changed the code file but failed to install it. :-( I am sorry for not checking the obvious. Kind regards Thiemo Quoting Tom Lane : Thiemo Kellner writes: I created a function that ought to return a timestamptz (another

timestamp out of range while casting return value to function's return type

Hi all I created a function that ought to return a timestamptz (another likewise timestamp) but calling it, I get mentionied error. What do I miss? I tried to return a timestamp of the year 2000 to no avail. Call: select utils.get_max_timestamptz(); -- Function -- create or replace functio

Re: Need a DB layout gui

You also could try out DBVisualizer (https://www.dbvis.com/). It is available in a feature reduced free version. Feautre comaprison is at https://www.dbvis.com/features/feature-list/. I personally bought the pro to support development. I rarely use a pro feature. Quoting Zahir Lalani : Nav

Re: sequences

Hi Karl I did not double check with the doc whether the SQL you posted is valid (I guess it could by applying the defaults) however I do not see how sequences would govern the sending of data to users. Kind regards Thiemo Quoting Karl Martin Skoldebrand : Hi, I'm trying to troubleshoot

Re: Table inheritance over schema boundaries possible

Quoting Achilleas Mantzios : as of 10 (same with 11) you (IL) must be the owner of relation SCD.TEMPL_BK . Create your tables with a user who has correct privileges on both tables/schemas. Not what I hope for but was afraid of. thank you for the answer. -- Achilleas Mantzios IT DEV Lead I

Table inheritance over schema boundaries possible

Hi all I am wondering if table inheritance is possible over the boundaries of schemata and different owners. I have database act with schemata il and scd. When I issue as IL create table IL.INHERITANCE_TEST() inherits (SCD.TEMPL_BK); I get [Code: 0, SQL State: 42501] ERROR: permission den

Re: POSTGRES/MYSQL

Quoting Adrian Klaver : On 3/11/19 9:31 AM, Sonam Sharma wrote: Hi Adrian, Ours is retail company and the DB size is Max 30gb, currently we are using db2. Things to consider: 1) Migration tools for DB2 --> MySQL/Postgresql. I have not done this, so someone else will have to comment.

Re: Is there something wrong with my test case?

Hi Fabio Quoting Fabio Pardi : The cost is not a range. The 2 numbers you see are: * Estimated start-up cost. This is the time expended before the output phase can begin, e.g., time to do the sorting in a sort node. *Estimated total cost. This is stated on the assumption that

  1   2   >