Postgres INSERT timestamp with UNION ALL error-CodePudding

Good day!

I need to export/import data from SQL Server 2019 to AWS RDS running PostgreSQL 13.3

It's just a few hundred rows from a handful of tables.
This is my first ever encounter with Postgres, so I decided to simply script data as "INSERT ... SELECT", as I would with SQL Server... and I've looked into AWS Glue, RDS S3 Import - it all seems waaay too much for what I need.

I am using DBeaver v21 for of this as I have easy access to both source and destination DBs.

This I tested with success:


CREATE TABLE public.invoices (
    invoiceno int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    terminalid int4 NOT NULL,
    invoicedate timestamp NOT NULL,
    description varchar(100) NOT null
    );


INSERT INTO public.invoices(InvoiceNo,TerminalID,InvoiceDate,Description)
SELECT 7 as invoiceno , 5 as terminalid , '2018-10-24 21:29:00' as invoicedate , N'Coffe and cookie' as description 
-- Updated Rows 1
-- No problem here

I scripted the rest of the data with UNION ALL, like so (shortened example) :

INSERT INTO public.invoices(InvoiceNo,TerminalID,InvoiceDate,Description)
SELECT 7 as invoiceno , 5 as terminalid , '2018-10-24 21:29:00' as invoicedate , N'Coffe and cookie' as description  
UNION ALL
SELECT 1000, 5 , '2018-10-24 21:29:00' , N'Tea and crumpets'

and now I get:

SQL Error [42804]: ERROR: column "invoicedate" is of type timestamp without time zone but expression is of type text
  Hint: You will need to rewrite or cast the expression.
  Position: 118

I do see in the message that it can be "fixed" with a CAST (or rewrite!).... but how come Postgres can convert 1 row implicitly, yet 2 rows is impossible?
why does this fail when more than 1 row is being inserted? - it clearly knows how to convert text -> date ...
I tried using VALUES, CTE, derived tables with no success.
As I have to spend more time with postgres - I really would like to understand what's going on here. Is my syntax wrong (works fine SQL Server), is DBeaver messing up something with my data, etc...?

Any suggestions would be appreciated. Thank you

CodePudding user response：

'2018-10-24 21:29:00' is a string value and Postgres is a bit more picky about correct data types then SQL Server.

You need to specify the value as a proper timestamp constant,

timestamp '2018-10-24 21:29:00'

Note that you can write that in a bit more compact form using a values clause:

INSERT INTO public.invoices(InvoiceNo,TerminalID,InvoiceDate,Description)
values 
 (7, 5, timestamp '2018-10-24 21:29:00', 'Coffe and cookie'),
 (1000, 5 , timestamp '2018-10-24 21:29:00' , 'Tea and crumpets');

CodePudding user response：

The reason of such behaviour is in order of compilation. In situation when you use VIEW first are compiled querys in view and types of columns (names too) in view is taken from the first part of a "view" (the first SELECT command). So, you have got text instead of timestamp and it doesn't match to inserted table type.

MSSQL compiler is a little bit smarter :-).

In first example you have simple INSERT INTO ... SELECT .... and compiler at once expect timestamp type - so , it not rise any compilation error (but error can ocure in execution time when the data do not pass rules of automatic conversion).