executing a complex sql select for postgresql using c# npgsql

774 views Asked by At

I'm trying to execute a query that will get me a value from my postgre database using c# with the npgsql plugin. I got the query from here.

https://dba.stackexchange.com/a/90567

This is the query

string query = String.Format(@"SELECT a.attrelid::regclass::text, a.attname
                                             , CASE a.atttypid
                                                 WHEN 'int'::regtype  THEN 'serial'
                                                 WHEN 'int8'::regtype THEN 'bigserial'
                                                 WHEN 'int2'::regtype THEN 'smallserial'
                                               END AS serial_type
                                        FROM   pg_attribute  a
                                        JOIN   pg_constraint c ON c.conrelid  = a.attrelid
                                                              AND c.conkey[1] = a.attnum 
                                        JOIN   pg_attrdef   ad ON ad.adrelid  = a.attrelid
                                                              AND ad.adnum    = a.attnum
                                        WHERE  a.attrelid = '""public.{0}""'::regclass            
                                        AND    a.attnum > 0
                                        AND    NOT a.attisdropped
                                        AND    a.atttypid = ANY('{int,int8,int2}'::regtype[])
                                        AND    array_length(c.conkey, 1) = 1   
                                        AND    ad.adsrc = 'nextval('''
                                                    || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
                                                    || '''::regclass)';  ", record);

It's giving me a

Input string was not in a correct format.

When it goes through that variable. I only have 1 variable and that's the {0}, but I don't know why it's yelling at me with that.

EDIT:

Btw, the double quotes(") are needed because it is created using entity framework and that's just how it works when you execute the query on pgAdminIII. All the tables needs to be inside them.

0

There are 0 answers