deparse altered "CREATE TABLE" statement, removed WITHOUT TIME ZONE

[divyenduz]

Original query (with fingerprint 2c0d1f29c30f5177):

  CREATE TABLE public."AO_563AEE_ACTIVITY_ENTITY" (
    "ACTIVITY_ID" bigint NOT NULL,
    "ACTOR_ID" integer,
    "CONTENT" text,
    "GENERATOR_DISPLAY_NAME" character varying(255),
    "GENERATOR_ID" character varying(450),
    "ICON_ID" integer,
    "ID" character varying(450),
    "ISSUE_KEY" character varying(255),
    "OBJECT_ID" integer,
    "POSTER" character varying(255),
    "PROJECT_KEY" character varying(255),
    "PUBLISHED" timestamp without time zone,
    "TARGET_ID" integer,
    "TITLE" character varying(255),
    "URL" character varying(450),
    "USERNAME" character varying(255),
    "VERB" character varying(450)
  )

Deparsed query (with fingerprint 13401b610a7be108):

CREATE TABLE public.AO_563AEE_ACTIVITY_ENTITY (
 	ACTIVITY_ID bigint NOT NULL,
	ACTOR_ID int,
	CONTENT text,
	GENERATOR_DISPLAY_NAME varchar(255),
	GENERATOR_ID varchar(450),
	ICON_ID int,
	ID varchar(450),
	ISSUE_KEY varchar(255),
	OBJECT_ID int,
	POSTER varchar(255),
	PROJECT_KEY varchar(255),
	PUBLISHED timestamp,
	TARGET_ID int,
	TITLE varchar(255),
	URL varchar(450),
	USERNAME varchar(255),
	VERB varchar(450) 
);

• On field "PUBLISHED", the type was changed from timestamp without timezone to timestamp.

To reproduce:

const fs = require('fs')
const { parse, deparse } = require('pgsql-parser');

async function main() {
    const stmts = parse(`
        CREATE TABLE public."AO_563AEE_ACTIVITY_ENTITY" (
    "ACTIVITY_ID" bigint NOT NULL,
    "ACTOR_ID" integer,
    "CONTENT" text,
    "GENERATOR_DISPLAY_NAME" character varying(255),
    "GENERATOR_ID" character varying(450),
    "ICON_ID" integer,
    "ID" character varying(450),
    "ISSUE_KEY" character varying(255),
    "OBJECT_ID" integer,
    "POSTER" character varying(255),
    "PROJECT_KEY" character varying(255),
    "PUBLISHED" timestamp without time zone,
    "TARGET_ID" integer,
    "TITLE" character varying(255),
    "URL" character varying(450),
    "USERNAME" character varying(255),
    "VERB" character varying(450)
  )
    `)
    const stmts2 = deparse(stmts[0])
    console.log(stmts2)
}

main()

Schema at https://github.com/prisma/database-schema-examples/tree/main/postgres/jira

[pyramation]

so this is not technically a bug since timestamp is by definition without timezone. The AST produces the same syntax regardless of variations. Does that make sense?

https://www.postgresql.org/docs/9.1/datatype-datetime.html

So in your example, the type wasn't actually changed, but the syntax expressing it was.

[divyenduz]

Curious, it makes sense but I don't understand why the query fingerprint changed. Isn't finger print supposed to remain same for various syntaxes of the same query? 🤔

[pyramation]

two queries can create the same AST. One AST, however, can only create one query.

[divyenduz]

Thank you, but do you think that fingerprint of two queries which produce the same AST should be the same?

[pyramation]

what I'm suggesting is that, the deparser is not a bijective function, in other words It's lossy. So I think the fingerprints of two different queries should each be unique, however, once they are passed through the parser/deparser and back, they would only ever produce one query and thus one fingerprint.

[divyenduz]

Got it, thank you for taking the time to explain.